6.3.3 行值动态变化的交叉报表处理示例(转换多列).sql

sqlserver 数据库编程的绝好脚本

CREATE TABLE tb(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT tb SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red',  -23
UNION ALL SELECT 'bb','Cup'  ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red',  -90

--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT Groups'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
		+','+QUOTENAME(Item)
		+N'=SUM(CASE Item WHEN '+QUOTENAME(Item,N'''')
		+N' THEN Quantity END)'
FROM tb
GROUP BY Item
--生成列记录水平显示的处理代码拼接(处理Color列)
SELECT @s=@s
		+','+QUOTENAME(Color)
		+N'=SUM(CASE Color WHEN '+QUOTENAME(Color,N'''')
		+N' THEN Quantity END)'
FROM tb
GROUP BY Color
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY Groups')
/*--结果
Groups   Chair      Cup      Table     Blue     Green     Red 
------------ --------------- -------------- ------------- ------------ -------------- ----------- 
aa       11         NULL    124      225      NULL    -90
bb       NULL     -23       -23      NULL    -23       -23
--*/