行转列，这个需求有点变态
create table DispatchList
(
DLID int IDENTITY(1,1) not null,--发货主表标识
cDLCode nvarchar(30),---发货单号
dDate datetime,---发货日期
cSOCode nvarchar(30),---销售订单号
)
create table DispatchLists
(
AutoID int IDENTITY(1,1) NOT NULL,--发货单子表标识
DLID int,--发货单主表标识
Name nvarchar(50),---产品名称
iQuantity int,---数量
price int,---单价
iSum int,-- 小计=单价*数量
)
insert into DispatchList values('880922001-1','2013-9-22','880922001')
insert into DispatchList values('880922001-2','2013-9-23','880922001')
insert into DispatchList values('880922001-3','2013-9-24','880922001')
insert into DispatchList values('880922001-4','2013-9-25','880922001')
insert into DispatchList values('880922001-5','2013-9-26','880922001')
--第一次发货
insert into DispatchLists values(1,'A',10,50,500)
insert into DispatchLists values(1,'B',10,60,600)
insert into DispatchLists values(1,'C',5,70,350)
insert into DispatchLists values(1,'D',10,80,800)
--第二次发货
insert into DispatchLists values(2,'B',10,60,600)
insert into DispatchLists values(2,'C',5,70,350)
insert into DispatchLists values(2,'D',15,80,15*80)
--第三次发货
insert into DispatchLists values(3,'B',10,60,600)
insert into DispatchLists values(3,'C',5,70,350)
insert into DispatchLists values(3,'D',5,80,5*80)
--第四次发货
insert into DispatchLists values(4,'C',5,70,350)
insert into DispatchLists values(4,'E',5,80,5*80)
--第五次发货
insert into DispatchLists values(5,'C',15,70,15*70)
insert into DispatchLists values(5,'D',5,80,5*80)
SELECT a.*, b.* FROM DispatchList AS a LEFT JOIN DispatchLists AS b ON a.DLID = b.DLID
--求行转列结果：
--销售订单号 产品名称 发货日期1 发货数量1 发货金额1 发货日期2 发货数量2 发货金额2 发货日期3 发货数量3 发货金额3 累计发货数量
--880922001 A 2013-9-22 10 500 10
--880922001 B 2013-9-22 10 600 2013-9-23 10 600 2013-9-24 10 600 30
--880922001 C 2013-9-22 5 350 2013-9-23 5 350 2013-9-26 5+5+15 350+350+1050 35
--880922001 D 2013-9-22 10 800 2013-9-23 5 1200 2013-9-26 5+5 400+400 25
--880922001 E 2013-9-25 5 400 5
--第一次发货
--发货日期1=第一次发货日期，发货数量1=第一次发货数量，发货金额1=第一次发货金额
--第二次发货
--发货日期2=第二次发货日期，发货数量2=第二次发货数量，发货金额2=第二次发货金额
--第三次发货
--第四次发货
--发货数量3=第三次发货数量+第四次发货数量+N，发货日期3=最后一次发货时间，发货金额3=第三次发货金额+第四次发货金额+N 行转列 SQL ?数据库
[解决办法]
select cSOCode,Name,
max(case when right(cDLCode,1)='1' then dDate else null end) as rq1,
max(case when right(cDLCode,1)='1' then iQuantity else null end) as sl1,
max(case when right(cDLCode,1)='1' then iSum else null end) as je1,
max(case when right(cDLCode,1)='2' then dDate else null end) as rq2,
max(case when right(cDLCode,1)='2' then iQuantity else null end) as sl2,
max(case when right(cDLCode,1)='2' then iSum else null end) as je2,
max(case when right(cDLCode,1)>='3' then dDate else null end) as rq3,
sum(case when right(cDLCode,1)>='3' then iQuantity else null end) as sl3,
sum(case when right(cDLCode,1)>='3' then iSum else null end) as je3
from DispatchList a,DispatchLists b
where a.DLID=b.DLID
group by cSOCode,Name
880922001A2013/9/22 0:00:00.00010500[NULL][NULL][NULL][NULL][NULL][NULL]
880922001B2013/9/22 0:00:00.000106002013/9/23 0:00:00.000106002013/9/24 0:00:00.00010600
880922001C2013/9/22 0:00:00.00053502013/9/23 0:00:00.00053502013/9/26 0:00:00.000251750
880922001D2013/9/22 0:00:00.000108002013/9/23 0:00:00.0001512002013/9/26 0:00:00.00010800
880922001E[NULL][NULL][NULL][NULL][NULL][NULL]2013/9/25 0:00:00.0005400
select left(cDLCode,9) as cDLCode,Name
,max(case when right(cDLCode,1)='1' then dDate else null end) as rq1
,sum(case when right(cDLCode,1)='1' then iQuantity else null end) as sl1
,sum(case when right(cDLCode,1)='1' then iSum else null end) as je1
, max(case when right(cDLCode,1)='2' then dDate else null end) as rq2
,sum(case when right(cDLCode,1)='2' then iQuantity else null end) as sl2
,sum(case when right(cDLCode,1)='2' then iSum else null end) as je2
, max(case when right(cDLCode,1) >='3' then dDate else null end) as rq3
, sum(case when right(cDLCode,1)>='3' then iQuantity else null end) as sl3
, sum(case when right(cDLCode,1)>='3' then iSum else null end) as je3
,sum(iQuantity) sl
FROM DispatchList AS a LEFT JOIN DispatchLists AS b ON a.DLID = b.DLID
group by left(cDLCode,9),Name
--最后综合查询
select t.cSOCode as 销售订单号,t.Name as 产品名称,max(发货日期1) as 发货日期1,max(发货数量1) as 发货数量1,max(发货金额1) as 发货金额1
,max(发货日期2) as 发货日期2,max(发货数量2) as 发货数量2,max(发货金额2) as 发货金额2
,max(发货日期3) as 发货日期3,max(发货数量3) as 发货数量3,max(发货金额3) as 发货金额3
,sum(isnull(发货数量1,0)+isnull(发货数量2,0)+isnull(发货数量3,0)) as 累计发货数量 --->这里空值转换为0,否则累加错误
from
(select *
from (select distinct cSOCode from DispatchList)a,
(select distinct Name from DispatchLists)b
)t
left join (select a.cSOCode,b.Name,max(a.dDate) as 发货日期1,sum(b.iQuantity) as 发货数量1,sum(b.iSum) as 发货金额1
from DispatchList a
left join DispatchLists b on a.DLID=b.DLID
where b.DLID=1
group by a.cSOCode,b.Name
)t1 on t.cSOCode=t1.cSOCode and t.Name=t1.Name
left join (select a.cSOCode,b.Name,max(a.dDate) as 发货日期2,sum(b.iQuantity) as 发货数量2,sum(b.iSum) as 发货金额2
from DispatchList a
left join DispatchLists b on a.DLID=b.DLID
where b.DLID=2
group by a.cSOCode,b.Name
)t2 on t.cSOCode=t2.cSOCode and t.Name=t2.Name
left join (select a.cSOCode,b.Name,max(a.dDate) as 发货日期3,sum(b.iQuantity) as 发货数量3,sum(b.iSum) as 发货金额3
from DispatchList a
left join DispatchLists b on a.DLID=b.DLID
where b.DLID>=3
group by a.cSOCode,b.Name
)t3 on t.cSOCode=t3.cSOCode and t.Name=t3.Name
group by t.cSOCode,t.Name
/*
销售订单号 产品名称 发货日期1 发货数量1 发货金额1 发货日期2 发货数量2 发货金额2 发货日期3 发货数量3 发货金额3 累计发货数量
----------------------------------------------------
880922001A2013-09-22 00:00:00.00010500NULLNULLNULLNULLNULLNULL10
880922001B2013-09-22 00:00:00.000106002013-09-23 00:00:00.000106002013-09-24 00:00:00.0001060030
880922001C2013-09-22 00:00:00.00053502013-09-23 00:00:00.00053502013-09-26 00:00:00.00025175035
880922001D2013-09-22 00:00:00.000108002013-09-23 00:00:00.0001512002013-09-26 00:00:00.0001080035
880922001ENULLNULLNULLNULLNULLNULL2013-09-25 00:00:00.00054005
*/
