SQL 2008中多行转多列

Question

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。
 

Answer 1

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 C001docduedate
from 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.docentry
group 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-15
mp-a52       0           1900-01-01  1900-01-01     7           2012-05-01  2012-05-07     0           1900-01-01  1900-01-01
*/

Answer 2

因为docdate日期取最近的一笔.所以没有ak-530 10这笔.

Answer 3

if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([docentry] int,[cardcode] varchar(4),[docdate] datetime,[docduedate] datetime)
insert [tb1]
select 1,'A001','2012-01-01','2012-03-25' union all
select 2,'A001','2012-03-27','2012-03-31' union all
select 3,'B001','2012-05-01','2012-05-07' union all
select 4,'C001','2012-05-20','2012-06-15'
go
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([docentry] int,[itemcode] varchar(6),[price] int)
insert [tb2]
select 1,'ak-530',10 union all
select 1,'ak-cc',8 union all
select 2,'ak-530',99 union all
select 3,'mp-a52',7 union all
select 4,'ak-530',25
godeclare @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 25
mp-a52   NULL                    NULL                    NULL        2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 7           NULL                    NULL                    NULL(2 行受影响)
**/

Answer 4

例如ak-530,对应docentry 1,2,4
取最近的一笔docdate,应该是"4 C001 2012-05-20 2012-06-15"这一笔,
结果中为何又有ak-530 99

Answer 5

厂商cardcode不同，一个是A001,一个是C001.
当有相同厂商CARDCODE与相同ITEMCODE时就取最近的数据与不同CARDCODE相同ITEMCODE做比较

Answer 6

create table tab1
(docentry int, cardcode varchar(5), 
 docdate date, docduedate date)insert into tab1
select 1, 'A001', '2012-01-01', '2012-03-25' union all
select 2, 'A001', '2012-03-27', '2012-03-31' union all
select 3, 'B001', '2012-05-01', '2012-05-07' union all
select 4, 'C001', '2012-05-20', '2012-06-15'create table tab2
(docentry int, itemcode varchar(8), price int)insert into tab2
select 1, 'ak-530', 10 union all
select 1, 'ak-cc', 8 union all
select 2, 'ak-530', 99 union all
select 3, 'mp-a52', 7 union all
select 4, 'ak-530', 25
with 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)d
pivot(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  25
ak-cc    2012-01-01 2012-03-25  8          NULL       NULL        NULL       NULL       NULL        NULL
mp-a52   NULL       NULL        NULL       2012-05-01 2012-05-07  7          NULL       NULL        NULL(3 row(s) affected)
*/