SQL交叉报表

我们将用下图研究报表：

1、简单报表操作：

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

1. SELECT   
2. [Year],   
3. Q1=SUM(CASE WHEN [Quarter]=1 THEN Quantity END),   
4. Q2=SUM(CASE WHEN [Quarter]=2 THEN Quantity END),   
5. Q3=SUM(CASE WHEN [Quarter]=3 THEN Quantity END),   
6. Q4=SUM(CASE WHEN [Quarter]=4 THEN Quantity END)   
7. 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语句：

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

3、动态生成交叉报表的列：
这次生成的图表如上图 SQL可写作：

1. DECLARE @S NVARCHAR(4000)    
2. SET @S=''   
3. SELECT  @S=@S+'   
4. Q'+CAST([Quarter] AS NVARCHAR)+'_Quantity=SUM(CASE WHEN [Quarter]='+CAST([Quarter] AS NVARCHAR)+' THEN Quantity END),    
5. Q'+CAST([Quarter] AS NVARCHAR)+'_Price=CAST(AVG(CASE WHEN [Quarter]='+ CAST([Quarter] AS NVARCHAR) +' THEN Quantity END) AS DECIMAL(10,2)),   
6. 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]    
7. SET  @S='SELECT [Year],'+ SUBSTRING(@S,1,LEN(@S)-1)+' FROM tb GROUP BY [Year]'   
8. EXEC(@S)