SQL系列交叉报表

核心提示：我们将用下图研究报表：1、简单报表操作：上表经过下列运算后可产生：SELECT[Year],Q1=SUM(CASEWHEN[Quarter]=1THENQuantityEND),Q2=SUM(CASEWHEN[Quarter]=2THENQuantityEND),Q3=SUM(CASEWHEN[Quarter]=3TH

我们将用下图研究报表：

1、简单报表操作：

上表经过下列运算后可产生：

SELECT　　
[Year],　　
Q1=SUM(CASE　WHEN　[Quarter]=1　THEN　Quantity　END),　　
Q2=SUM(CASE　WHEN　[Quarter]=2　THEN　Quantity　END),　　
Q3=SUM(CASE　WHEN　[Quarter]=3　THEN　Quantity　END),　　
Q4=SUM(CASE　WHEN　[Quarter]=4　THEN　Quantity　END)　　
FROM　tb　GROUP　BY　[Year]　　

补充：除了SUM外，也可以用其他常用聚合函数例如AVG、MIN、SUM、STDEV、COUNT、STDEVP、VAR、GROUPING　、VARP、MAX　

再次补充：

STDEV　 返回给定表达式中所有值的统计标准偏差。

STDEVP 返回给定表达式中所有值的填充统计标准偏差。

VAR　　　 返回给定表达式中所有值的统计方差。

VARP　　 返回给定表达式中所有值的填充统计方差。

注意：以上聚合函数 都只能用于数字列

GROUPING　 常和CUBE和ROLLUP结合使用 如果是附加列输出1否则输出0

2、涉及多列的交叉报表

查看原图（大图）

SQL语句：

SELECT　[Year],　　

Q1_Quantity=SUM(CASE　WHEN　[Quarter]=1　THEN　Quantity　END),　　
Q1_Price=CAST(AVG(CASE　WHEN　[Quarter]=1　THEN　Quantity　END)　AS　DECIMAL(10,2)),　　
Q1_Money=SUM(CASE　WHEN　[Quarter]=1　THEN　CAST(Quantity*Price　AS　DECIMAL(10,2))　END),　　
Q2_Quantity=SUM(CASE　WHEN　[Quarter]=2　THEN　Quantity　END),　　
Q2_Price=CAST(AVG(CASE　WHEN　[Quarter]=2　THEN　Quantity　END)　AS　DECIMAL(10,2)),　　
Q2_Money=SUM(CASE　WHEN　[Quarter]=2　THEN　CAST(Quantity*Price　AS　DECIMAL(10,2))　END),　　
Q3_Quantity=SUM(CASE　WHEN　[Quarter]=3　THEN　Quantity　END),　　
Q3_Price=CAST(AVG(CASE　WHEN　[Quarter]=3　THEN　Quantity　END)　AS　DECIMAL(10,2)),　　
Q3_Money=SUM(CASE　WHEN　[Quarter]=3　THEN　CAST(Quantity*Price　AS　DECIMAL(10,2))　END),　　
Q4_Quantity=SUM(CASE　WHEN　[Quarter]=4　THEN　Quantity　END),　　
Q4_Price=CAST(AVG(CASE　WHEN　[Quarter]=4　THEN　Quantity　END)　AS　DECIMAL(10,2)),　　
Q4_Money=SUM(CASE　WHEN　[Quarter]=4　THEN　CAST(Quantity*Price　AS　DECIMAL(10,2))　END)　　
FROM　tb　　GRO<span　style="color:　#ff0000">UP　BY　[Year]　　
</span>

3、动态生成交叉报表的列：

这次生成的图表如上图 SQL可写作：

DECLARE　@S　NVARCHAR(4000)　　　
SET　@S=''　　
SELECT　　@S=@S+'　　
Q'+CAST([Quarter]　AS　NVARCHAR)+'_Quantity=SUM(CASE　WHEN　[Quarter]='+CAST([Quarter]　AS　NVARCHAR)+'　THEN　Quantity　END),　　　
Q'+CAST([Quarter]　AS　NVARCHAR)+'_Price=CAST(AVG(CASE　WHEN　[Quarter]='+　CAST([Quarter]　AS　NVARCHAR)　+'　THEN　Quantity　END)　AS　DECIMAL(10,2)),　　
Q'+CAST([Quarter]　AS　NVARCHAR)+'_Money=SUM(CASE　WHEN　[Quarter]='+　CAST([Quarter]　AS　NVARCHAR)　+'　THEN　CAST(Quantity*Price　AS　DECIMAL(10,2))　END),'　FROM　tb　　GROUP　BY　[Quarter]　　　
SET　　@S='SELECT　[Year],'+　SUBSTRING(@S,1,LEN(@S)-1)+'　FROM　tb　GROUP　BY　[Year]'　　
　
EXEC(@S)