一个行列转换结构，100分求支招！解决方法

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

SQL code

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

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

SQL code

时间(月)     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 是动态的，不要写死！！！

[解决办法]
做个标记，这活我接
[解决办法]
这小活大神都接~我闪
[解决办法]
建议如下：
1、建临时表 含字段 
时间(月) A的Qty1 A的Qty2 A的Qty3 A的Qty4 B的Qty1 B的Qty2 B的Qty3 B的Qty4

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

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

4、查询临时表，得出结果
[解决办法]

SQL code

--> 测试数据：[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 allselect 'B',200,330,250,900,'2012-02-01' union allselect 'C',150,150,350,300,'2012-03-01' union allselect '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 @strexec(@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的quty41    100    200    300    400    0    0    0    0    0    0    0    0    100    240    300    4802    0    0    0    0    200    330    250    900    0    0    0    0    0    0    0    03    0    0    0    0    0    0    0    0    150    150    350    300    0    0    0    0*/
[解决办法]
SQL code
--> 测试数据：[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 allselect 'B',200,330,250,900,'2012-02-01' union allselect 'C',150,150,350,300,'2012-03-01' union allselect 'D',100,240,300,480,'2012-01-01'--------------开始查询----------------------------select * from [tb] cross join (select [FNumber] from [tb]) bdeclare @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 tbgroup 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         4802           0           0           0           0           200         330         250         900         0           0           0           0           0           0           0           03           0           0           0           0           0           0           0           0           150         150         350         300         0           0           0           0(3 行受影响)*/ 
 
[解决办法]
应该是sum  



SQL code
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 tbgroup 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         4802           0           0           0           0           200         330         250         900         0           0           0           0           0           0           0           03           0           0           0           0           0           0           0           0           150         150         350         300         0           0           0           0(3 行受影响)*/ 

 

查看更多 下一篇