一个行列转换结构，100分求支招！！！！

[lybjust]

有表结构：

FNumber      Qty1      Qty2      Qty3      Qty4      FDate
A            100       200       300       400       2012-01-01
B            200       330       250       900       2012-02-01
C            150       150       350       300       2012-03-01
D            100       240       300       480       2012-01-01

现在需要查询出如下按月统计效果：

时间(月)     A的Qty1   A的Qty2   A的Qty3   A的Qty4   B的Qty1   B的Qty2   B的Qty3   B的Qty4   ..................
1            100       200       300       400       0         0         0         0       ..................
2            0         0         0         0         200       330       250       900     ..................
3            ..................

不知道大家看懂没有，其中涉及到的主要关键点应该是行列转换的表示，求支招？
A B C D 是动态的，不要写死！！！

[开着拖拉机泡妞]

做个标记，这活我接

[APHY]

这小活大神都接~我闪

[simonxt]

建议如下：
1、建临时表 含字段
时间(月) A的Qty1 A的Qty2 A的Qty3 A的Qty4 B的Qty1 B的Qty2 B的Qty3 B的Qty4

2、将“时间(月)”对应列数据更新（插入n条你要的时间记录）

3、将你的数据循环插入该临时表

4、查询临时表，得出结果

[开着拖拉机泡妞]

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl](
[FNumber] varchar(1),
[Qty1] int,
[Qty2] int,
[Qty3] int,
[Qty4] int,
[FDate] datetime
)
insert [tbl]
select 'A',100,200,300,400,'2012-01-01' union all
select 'B',200,330,250,900,'2012-02-01' union all
select 'C',150,150,350,300,'2012-03-01' union all
select 'D',100,240,300,480,'2012-01-01'

declare @str varchar(max)
set @str=''
select @str=@str+
',['+[FNumber]+'的quty1]'+'=sum(case when [FNumber]='+QUOTENAME([FNumber],'''')+
' then [Qty1] else 0 end),'+[FNumber]+'的quty2'+'=sum(case when [FNumber]='+QUOTENAME([FNumber],'''')+
' then [Qty2] else 0 end),'+[FNumber]+'的quty3'+'=sum(case when [FNumber]='+QUOTENAME([FNumber],'''')+
' then [Qty3] else 0 end),'+[FNumber]+'的quty4'+'=sum(case when [FNumber]='+QUOTENAME([FNumber],'''')+
' then [Qty4] else 0 end)'
from tbl group by [FNumber]
set @str='select ltrim(month([FDate])) as [时间(月)]'+@str+' from tbl group by ltrim(month([FDate]))'
print @str
exec(@str)
/*
时间(月)	A的quty1	A的quty2	A的quty3	A的quty4	B的quty1	B的quty2	B的quty3	B的quty4	C的quty1	C的quty2	C的quty3	C的quty4	D的quty1	D的quty2	D的quty3	D的quty4
1	100	200	300	400	0	0	0	0	0	0	0	0	100	240	300	480
2	0	0	0	0	200	330	250	900	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0	150	150	350	300	0	0	0	0
*/

[黄_瓜]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([FNumber] varchar(1),[Qty1] int,[Qty2] int,[Qty3] int,[Qty4] int,[FDate] datetime)
insert [tb]
select 'A',100,200,300,400,'2012-01-01' union all
select 'B',200,330,250,900,'2012-02-01' union all
select 'C',150,150,350,300,'2012-03-01' union all
select 'D',100,240,300,480,'2012-01-01'
--------------开始查询--------------------------

--select * from [tb] cross join (select [FNumber] from [tb]) b
declare @s varchar(max)
set @s=''
select @s=@s+','+FNumber+'Qty1=max(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty1] else 0 end),'
+FNumber+'Qty2=max(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty2] else 0 end),'
+FNumber+'Qty3=max(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty3] else 0 end),'
+FNumber+'Qty4=max(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty4] else 0 end)'
from tb
group by [FNumber]
select @s='select month(FDate) as [month]'+@s+' from [tb] group by month([FDate])'
exec (@s)

----------------结果----------------------------
/* 
month       AQty1       AQty2       AQty3       AQty4       BQty1       BQty2       BQty3       BQty4       CQty1       CQty2       CQty3       CQty4       DQty1       DQty2       DQty3       DQty4
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           100         200         300         400         0           0           0           0           0           0           0           0           100         240         300         480
2           0           0           0           0           200         330         250         900         0           0           0           0           0           0           0           0
3           0           0           0           0           0           0           0           0           150         150         350         300         0           0           0           0

(3 行受影响)

*/

[黄_瓜]

应该是sum

declare @s varchar(max)
select @s=isnull(@s+',','')+FNumber+'Qty1=sum(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty1] else 0 end),'
+FNumber+'Qty2=sum(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty2] else 0 end),'
+FNumber+'Qty3=sum(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty3] else 0 end),'
+FNumber+'Qty4=sum(case when [FNumber]='+quotename(FNumber,'''')+' then [Qty4] else 0 end)'
from tb
group by [FNumber]
select @s='select month(FDate) as [month],'+@s+' from [tb] group by month([FDate])'
exec (@s)

----------------结果----------------------------
/* 
month       AQty1       AQty2       AQty3       AQty4       BQty1       BQty2       BQty3       BQty4       CQty1       CQty2       CQty3       CQty4       DQty1       DQty2       DQty3       DQty4
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           100         200         300         400         0           0           0           0           0           0           0           0           100         240         300         480
2           0           0           0           0           200         330         250         900         0           0           0           0           0           0           0           0
3           0           0           0           0           0           0           0           0           150         150         350         300         0           0           0           0

(3 行受影响)

*/