新手求助,关于SQL 2005中问题。万分感谢!!
问题是,根据GoodsId,GoodsName,进行一个分月加总 ,并行转列。下面是演示数据,小弟在此先谢过了。
GoodsId GoodsName Date Qty
1a 2012/03/10 100
1a 2012/03/17 100
1a 2012/03/24 100
1a 2012/03/31 100
2b 2012/04/07 100
2b 2012/04/14 100
2b 2012/04/21 100
2b 2012/04/28 100
1a 2012/04/07 100
1a 2012/04/14 100
1a 2012/04/21 100
1a 2012/04/28 100
result
GoodsId GoodsName 03/10 03/10 03/24 03/31 March_Sum 04/07 04/14 04/21 04/28 April_Sum
1a 100 100 100 100 400 100 100100 100 400
2b 0 0 0 0 0 100 100100 100 400
[解决办法]
--> 测试数据:[test]goif object_id('[test]') is not null drop table [test]gocreate table [test]([GoodsId] int,[GoodsName] varchar(1),[Date] date,[Qty] int)goinsert [test]select 1,'a','2012/03/10',100 union allselect 1,'a','2012/03/17',100 union allselect 1,'a','2012/03/24',100 union allselect 1,'a','2012/03/31',100 union allselect 2,'b','2012/04/07',100 union allselect 2,'b','2012/04/14',100 union allselect 2,'b','2012/04/21',100 union allselect 2,'b','2012/04/28',100 union allselect 1,'a','2012/04/07',100 union allselect 1,'a','2012/04/14',100 union allselect 1,'a','2012/04/21',100 union allselect 1,'a','2012/04/28',100declare @str varchar(max)set @str=''select @str=@str+','+quotename([Date],'')+'=max(case when [Date]='+QUOTENAME([Date],'''')+'then [Qty] else 0 end)' from(select [GoodsId],[GoodsName],convert(varchar(5),[Date],101)[Date],[Qty] from testunion all select [GoodsId],[GoodsName],'['+ltrim(DATEPART(MM,[Date]))+'月份]' as [Date],SUM([Qty]) [Qty] from test group by [GoodsId],[GoodsName],'['+ltrim(DATEPART(MM,[Date]))+'月份]')agroup by [Date]set @str='select [GoodsId],[GoodsName]'+@str+' from (select [GoodsId],[GoodsName],convert(varchar(5),[Date],101)[Date],[Qty] from testunion all select [GoodsId],[GoodsName],''[''+ltrim(DATEPART(MM,[Date]))+''月份]'' as [Date],SUM([Qty]) [Qty] from test group by [GoodsId],[GoodsName],''[''+ltrim(DATEPART(MM,[Date]))+''月份]'')a group by [GoodsId],[GoodsName]' exec(@str) /*GoodsId GoodsName 03/10 03/17 03/24 03/31 [3月份] 04/07 04/14 04/21 04/28 [4月份]1 a 100 100 100 100 400 100 100 100 100 4002 b 0 0 0 0 0 100 100 100 100 400 */