又遇行轉列問題。。請各位幫忙。謝謝
Style deliverydate   qty
GG01       2007/1/5         20
GG01       2007/1/6         30
GG02       2007/1/5         40
GG03     2007/1/6         50

同一款號group   一起。希望得到的結果是

GG01   2007/1/5   20   2007/1/6   30
GG02   2007/1/5   40
GG03   2007/1/6   50

------解决方案--------------------
create table t(xh varchar(20),kc varchar(20),cj int)
insert t select '051000333 ', '高等数学 ',55
union all select '051000333 ', '大学语文 ',67
union all select '051000333 ', '经济学基础 ',88
union all select '021000224 ', '高等数学 ',64
union all select '021000224 ', '大学语文 ',32
union all select '021000224 ', '经济学基础 ',75
union all select '041000851 ', '高等数学 ',69
union all select '041000851 ', '大学语文 ',75
union all select '041000851 ', '经济学基础 ',65


declare @sql varchar(8000)
set @sql = 'select xh '
select @sql = @sql + ' , sum(case kc when ' ' ' + kc + ' ' ' then cj else 0 end) [ ' + kc + '] '
from (select distinct kc from t) as a
set @sql = @sql + ' from t group by xh '
exec(@sql)
------解决方案--------------------
create table test(Style varchar(10),deliverydate varchar(20),qty int)
insert test select 'GG01 ', '2007/1/5 ',20
union all select 'GG01 ', '2007/1/6 ',30
union all select 'GG02 ', '2007/1/5 ',40
union all select 'GG03 ', '2007/1/6 ',50

declare @s varchar(8000)
set @s= 'select Style '
select @s=@s+ ',max(case bh when ' ' '+rtrim(bh)+ ' ' ' then deliverydate else ' ' ' ' end) as deliverydate '+rtrim(bh)+ '
,max(case bh when ' ' '+rtrim(bh)+ ' ' ' then qty else null end) as qty '+rtrim(bh)+ ' '
from (select *,bh=(select count(1) from test where Style=b1.Style and deliverydate <=b1.deliverydate) from test b1)t group by bh

select @s=@s+ ' from (select *,bh=(select count(1) from test where Style=b1.Style
and deliverydate <=b1.deliverydate) from test b1)t group by Style '
exec(@s)

Style deliverydate1 qty1 deliverydate2 qty2
---------- -------------------- ----------- -------------------- -----------
GG01 2007/1/5 20 2007/1/6 30
GG02 2007/1/5 40 NULL
GG03 2007/1/6 50 NULL

警告: 聚合或其它 SET 操作消除了空值。
------解决方案--------------------
select c.*,d.deliverdate1,d.qty1
from t c
left join
(select Style,deliverydate as deliverdate1 ,qty as qty1 from t where exists(select 1 from t a where t.Style=a.Style and t.deliverydate <a.deliverydate))d on c.Style=d.Style
where not exists(select 1 from t a where c.Style=a.Style and c.deliverydate> a.deliverydate)


Style deliverydate qty deliverdate1 qty1
-------------------- -------------------- ----------- -------------------- -----------
GG01 2007/1/5 20 2007/1/5 20
GG02 2007/1/5 40