行转列汇总,该怎么解决

2012-04-08

行转列汇总产品编号区间销量1000120120330100012012014010001201202501000220120110100022012022010002201

行转列汇总
产品编号区间销量
10001 201203 30
10001 201201 40
10001 201202 50
10002 201201 10
10002 201202 20
10002 201203 30

如何将上述数据写入到，现有如下的数据表中
如果本月是2月份
产品 M-1月 M月 M+1月
10001 40 50 30
10002 10 20 30

[解决办法]

SQL code

--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([产品编号] int,[区间] varchar(6),[销量] int)insert [tbl]select 10001,'201203',30 union allselect 10001,'201201',40 union allselect 10001,'201202',50 union allselect 10002,'201201',10 union allselect 10002,'201202',20 union allselect 10002,'201203',30declare @str varchar(max)set @str=''select @str=@str+','+'['+RIGHT([区间],2)+'月]'+'=max(case when RIGHT([区间],2)='+QUOTENAME(RIGHT([区间],2),'''')+' then [销量] else 0 end)' from tblgroup by RIGHT([区间],2)exec('select [产品编号]'+@str+' from tbl group by [产品编号]')/*产品编号    01月    02月    03月10001    40    50    3010002    10    20    30*/
[解决办法]
SQL code--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([产品编号] int,[区间] varchar(6),[销量] int)insert [tbl]select 10001,'201203',30 union allselect 10001,'201201',40 union allselect 10001,'201202',50 union allselect 10002,'201201',10 union allselect 10002,'201202',20 union allselect 10002,'201203',30 union allselect 10002,'201204',30 union allselect 10001,'201203',30goif OBJECT_ID('pro_tracy')is not nulldrop proc pro_tracygocreate proc pro_tracyascreate table #t([产品编号] int,[区间] varchar(6),[销量] int)insert #tselect * from tbl where cast(RIGHT([区间],2) as Int) between MONTH(GETDATE())-1 and MONTH(GETDATE())+1declare @str varchar(max)set @str=''select @str=@str+','+'['+RIGHT([区间],2)+'月]'+'=max(case when RIGHT([区间],2)='+QUOTENAME(RIGHT([区间],2),'''')+' then [销量] else 0 end)' from #tgroup by RIGHT([区间],2)exec('select [产品编号]'+@str+' from #t group by [产品编号]')--修改了你的数据exec pro_tracy/*产品编号    02月    03月    04月10001    50    30    010002    20    30    30*/

热点排行

Bad Request.

SQL Server

行转列汇总,该怎么解决