SQL 2008中多行转多列,该怎么处理

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*/
------解决方案--------------------
SQL code
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,[email protected]  +' 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 行受影响)**/