交叉报表示例

[color=blue]1、简单交叉报表示例：[/color]
DECLARE @t TABLE(Year int,Quarter int,Amount decimal(10,1))
INSERT @t SELECT 1990, 1, 1.1
UNION ALL SELECT 1990, 2, 1.2
UNION ALL SELECT 1990, 3, 1.3
UNION ALL SELECT 1990, 4, 1.4
UNION ALL SELECT 1991, 1, 2.1
UNION ALL SELECT 1991, 2, 2.2
UNION ALL SELECT 1991, 3, 2.3
UNION ALL SELECT 1991, 4, 2.4

--查询处理
SELECT Year,
Q1=SUM(CASE Quarter WHEN 1 THEN Amount END),
Q2=SUM(CASE Quarter WHEN 2 THEN Amount END),
Q3=SUM(CASE Quarter WHEN 3 THEN Amount END),
Q4=SUM(CASE Quarter WHEN 4 THEN Amount END)
FROM @t
GROUP BY Year

/*--结果
Year Q1 Q2 Q3 Q4
--------- ------------------------------
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
--*/

[color=blue]2、动态交叉报表示例[/color]
CREATE TABLE tb(Year int,Quarter int,Quantity decimal(10,1),Price decimal(10,2))
INSERT tb SELECT 1990, 1, 1.1, 2.5
UNION ALL SELECT 1990, 1, 1.2, 3.0
UNION ALL SELECT 1990, 2, 1.2, 3.0
UNION ALL SELECT 1990, 1, 1.3, 3.5
UNION ALL SELECT 1990, 2, 1.4, 4.0
UNION ALL SELECT 1991, 1, 2.1, 4.5
UNION ALL SELECT 1991, 2, 2.1, 4.5
UNION ALL SELECT 1991, 2, 2.2, 5.0
UNION ALL SELECT 1991, 1, 2.3, 5.5
UNION ALL SELECT 1991, 1, 2.4, 6.0

--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT Year'
--生成列记录水平显示的处理代码拼接
SELECT @s=@s
+','+QUOTENAME(N'Q'+CAST(Quarter as varchar)+N'_Amount')
+N'=SUM(CASE Quarter WHEN '+CAST(Quarter as varchar)
+N' THEN Quantity END)'
+','+QUOTENAME(N'Q'+CAST(Quarter as varchar)+N'_Price')
+N'=CAST(AVG(CASE Quarter WHEN '+CAST(Quarter as varchar)
+N' THEN Price END)AS DECIMAL(10,2))'
+','+QUOTENAME(N'Q'+CAST(Quarter as varchar)+N'_Money')
+N'=CAST(SUM(CASE Quarter WHEN '+CAST(Quarter as varchar)
+N' THEN Quantity*Price END)AS DECIMAL(10,2))'
FROM tb
GROUP BY Quarter
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY Year')
/*--结果
Year Q1_Amount Q1_Price Q1_Money Q2_Amount Q2_Price Q2_Money
------- ----------------- --------------- ----------------- ----------------- -------------- ----------------
1990 3.6 3.00 10.90 2.6 3.50 9.20
1991 6.8 5.33 36.50 4.3 4.75 20.45
--*/