SQL 2008中多行转多列
RT：测试数据如下
表1
docentry cardcode docdate docduedate
1 A001 2012-01-01 2012-03-25
2 A001 2012-03-27 2012-03-31
3 B001 2012-05-01 2012-05-07
4 C001 2012-05-20 2012-06-15
表2
docentry itemcode price
1 ak-530 10
1 ak-cc 8
2 ak-530 99
3 mp-a52 7
4 ak-530 25
需求结果:
itemcode docdate docduedate A001 DOCDATE DOCDUEDATE C001
ak-530 2012-03-27 2012-03-31 99 2012-05-20 2012-06-15 25
就是需要按不同厂商（cardcode）相同物料号(itemcode)与最近的DOCDATE来对比不同厂商的PRICE。
[解决办法]
create table t1(docentry int,cardcode varchar(10),docdate date,docduedate date);insert into t1 values(1 ,'A001' ,'2012-01-01' ,'2012-03-25'),(2 ,'A001' ,'2012-03-27' ,'2012-03-31'),(3 ,'B001' ,'2012-05-01' ,'2012-05-07'),(4 ,'C001' ,'2012-05-20' ,'2012-06-15');create table t2(docentry int,itemcode varchar(100),price int);insert into t2 values(1,'ak-530', 10),(1, 'ak-cc', 8),(2, 'ak-530', 99),(3, 'mp-a52' ,7),(4, 'ak-530' ,25);-- 假设这里只有三种类型select itemcode,sum(case when cardcode='A001' then price else 0 end) as A001,max(case when cardcode='A001' then docdate else '' end) as A001docdate,max(case when cardcode='A001' then docduedate else '' end) as A001docduedate,sum(case when cardcode='B001' then price else 0 end) as B001,max(case when cardcode='B001' then docdate else '' end) as B001docdate,max(case when cardcode='B001' then docduedate else '' end) as B001docduedate,sum(case when cardcode='C001' then price else 0 end) as C001,max(case when cardcode='C001' then docdate else '' end) as C001docdate,max(case when cardcode='C001' then docduedate else '' end) as C001docduedatefrom t2 a join (select * from t1 x where not exists(select 1 from t1 where cardcode=x.cardcode and docdate>x.docdate)) b on a.docentry=b.docentrygroup by itemcode/*itemcode A001 A001docdate A001docduedate B001 B001docdate B001docduedate C001 C001docdate C001docduedate------------ ----------- ----------- -------------- ----------- ----------- -------------- ----------- ----------- --------------ak-530 99 2012-03-27 2012-03-31 0 1900-01-01 1900-01-01 25 2012-05-20 2012-06-15mp-a52 0 1900-01-01 1900-01-01 7 2012-05-01 2012-05-07 0 1900-01-01 1900-01-01*/
[解决办法]
if object_id('[tb1]') is not null drop table [tb1]gocreate table [tb1]([docentry] int,[cardcode] varchar(4),[docdate] datetime,[docduedate] datetime)insert [tb1]select 1,'A001','2012-01-01','2012-03-25' union allselect 2,'A001','2012-03-27','2012-03-31' union allselect 3,'B001','2012-05-01','2012-05-07' union allselect 4,'C001','2012-05-20','2012-06-15'goif object_id('[tb2]') is not null drop table [tb2]gocreate table [tb2]([docentry] int,[itemcode] varchar(6),[price] int)insert [tb2]select 1,'ak-530',10 union allselect 1,'ak-cc',8 union allselect 2,'ak-530',99 union allselect 3,'mp-a52',7 union allselect 4,'ak-530',25godeclare @sql varchar(8000)select @sql=isnull(@sql+',','') +'max(case when a.cardcode='''+cardcode+''' then a.docdate end) as [docdate'+cardcode+'],' +'max(case when a.cardcode='''+cardcode+''' then a.docduedate end) as [docduedate'+cardcode+'],' +'max(case when a.cardcode='''+cardcode+''' then b.price end) as ['+cardcode+']'from(select distinct cardcode from tb1) tset @sql='select b.itemcode,'+@sql +' from tb1 a join tb2 b on a.docentry=b.docentry' +' where not exists(select 1 from tb1 where cardcode=a.cardcode and docdate>a.docdate)' +' group by b.itemcode'exec (@sql)/**itemcode docdateA001 docduedateA001 A001 docdateB001 docduedateB001 B001 docdateC001 docduedateC001 C001-------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- -----------ak-530 2012-03-27 00:00:00.000 2012-03-31 00:00:00.000 99 NULL NULL NULL 2012-05-20 00:00:00.000 2012-06-15 00:00:00.000 25mp-a52 NULL NULL NULL 2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 7 NULL NULL NULL(2 行受影响)**/
[解决办法]
create table tab1(docentry int, cardcode varchar(5), docdate date, docduedate date)insert into tab1select 1, 'A001', '2012-01-01', '2012-03-25' union allselect 2, 'A001', '2012-03-27', '2012-03-31' union allselect 3, 'B001', '2012-05-01', '2012-05-07' union allselect 4, 'C001', '2012-05-20', '2012-06-15'create table tab2(docentry int, itemcode varchar(8), price int)insert into tab2select 1, 'ak-530', 10 union allselect 1, 'ak-cc', 8 union allselect 2, 'ak-530', 99 union allselect 3, 'mp-a52', 7 union allselect 4, 'ak-530', 25with c as(select b.docentry,b.itemcode,b.price, a.cardcode,a.docdate,a.docduedate, row_number() over(partition by b.itemcode,a.cardcode order by a.docdate desc) rn from tab2 b inner join tab1 a on b.docentry=a.docentry)select itemcode,(select max(docdate) from c f where f.rn=1 and f.itemcode=itemcode and f.price=[A001] and f.cardcode='A001') docdate, (select max(docduedate) from c f where f.rn=1 and f.itemcode=itemcode and f.price=[A001] and f.cardcode='A001') docduedate, [A001],(select max(docdate) from c f where f.rn=1 and f.itemcode=itemcode and f.price=[B001] and f.cardcode='B001') docdate, (select max(docduedate) from c f where f.rn=1 and f.itemcode=itemcode and f.price=[B001] and f.cardcode='B001') docduedate, [B001],(select max(docdate) from c f where f.rn=1 and f.itemcode=itemcode and f.price=[C001] and f.cardcode='C001') docdate, (select max(docduedate) from c f where f.rn=1 and f.itemcode=itemcode and f.price=[C001] and f.cardcode='C001') docduedate, [C001]from (select itemcode,cardcode,price from c where rn=1)dpivot(max(price) for cardcode in([A001],[B001],[C001])) e/*itemcode docdate docduedate A001 docdate docduedate B001 docdate docduedate C001-------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ---------- -----------ak-530 2012-03-27 2012-03-31 99 NULL NULL NULL 2012-05-20 2012-06-15 25ak-cc 2012-01-01 2012-03-25 8 NULL NULL NULL NULL NULL NULLmp-a52 NULL NULL NULL 2012-05-01 2012-05-07 7 NULL NULL NULL(3 row(s) affected)*/
[解决办法]
PIVOT