MS SQL 行转列的问题

[zts107]

联合查询后得到如下下表：


Fbillno Finterid Fstatus Fitemid Fauxqty Fdate Fqty

WORK005525 6595 3 28086 100.0000000000 2012-05 100.0000000000
WORK005526 6602 3 28090 300.0000000000 2012-05 174.0000000000
WORK005526 6602 3 28090 300.0000000000 2012-06 126.0000000000


Fdate字段是变动的，我想得到如下结果：
请教如何实现，谢谢！

Fbillno Finterid Fstatus Fitemid Fauxqty 2012-05 2012-06 Fqty

WORK005525 6595 3 28086 100.0000000000 100 0 100
WORK005526 6602 3 28090 300.0000000000 174 126 300

[shuobudeDE]

http://wenku.baidu.com/view/66ff110ef78a6529647d530c.html

[發糞塗牆]

http://blog.csdn.net/dba_huangzj/article/details/7530928我总结了一下例子，看看能不能帮上忙

[开着拖拉机泡妞]

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[Fbillno] varchar(10),
[Finterid] int,
[Fstatus] int,
[Fitemid] int,
[Fauxqty] numeric(13,10),
[Fdate] varchar(7),
[Fqty] numeric(5,2)
)
go
insert [test]
select 'WORK005525',6595,3,28086,100.0000000000,'2012-05',100.00 union all
select 'WORK005526',6602,3,28090,300.0000000000,'2012-05',174.00 union all
select 'WORK005526',6602,3,28090,300.0000000000,'2012-06',126.00

declare @str varchar(2000)
set @str=''
select 
    @str=@str+',['+[Fdate]
    +']=max(case when [Fdate]='
    +quotename([Fdate],'''')+' then [Fqty] else 0 end)'
from 
    test
group by 
    [Fdate]
print @str
set @str='select Fbillno,Finterid,Fstatus,Fitemid,Fauxqty'
     +@str+',sum(Fqty) as Fqty from test 
     group by Fbillno,Finterid,Fstatus,Fitemid,Fauxqty'
exec(@str)
/*
Fbillno	Finterid	Fstatus	Fitemid	Fauxqty	2012-05	2012-06	Fqty
------------------------------------------------------------------------------
WORK005525	6595	3	28086	100.0000000000	100.00	0.00	100.00
WORK005526	6602	3	28090	300.0000000000	174.00	126.00	300.00
*/

[owenwoshiawp]

select fbillno,finterid,fstatus,fitemid,fauxqty,
max(case when fdate='2012-05'then fqty else 0 end)[2012-05],
max(case when fdate='2012-06'then fqty else 0 end)[2012-06],
sum(fqty)[fqty]
from test group by fbillno,finterid,fstatus,fitemid,fauxqty