SQL行列转换，二次转换及添加‘小计‘、’总结’
原始Table数据
把MONTH列转为行头&添加总计、小计
1 --分類匯總
2 SELECT
3 ISNULL(PRDLB,'總計') AS PRDLB,
4 ISNULL([Month],'小計') AS [Month],
5 CASE WHEN PRDLB IS NULL THEN /*如果是总计列，（本月合-上月合）/上月*/
6 CONVERT(VARCHAR,convert(numeric(8,2),round((SUM(R1)-SUM(R2))*100/SUM(R2),2)))+'%'
7 ELSE
8 CONVERT(VARCHAR,SUM(Divide))+'%'
9 END AS Divide
10 INTO #TEMPRESULT_TOTAL
11 FROM #TEMPRESULT
12 GROUP BY PRDLB,[Month]
13 WITH CUBE
14 --
15 --行列轉換
16 DECLARE @STR NVARCHAR(MAX)
17 DECLARE @STR2 NVARCHAR(MAX)
18 DECLARE @SQL NVARCHAR(MAX)
19 SET @STR=''
20 SET @STR2=''
21 SET @SQL='SELECT PRDLB,'
22 SELECT @STR=@STR+',ISNULL(['+MONTH+'],0) AS ['+MONTH+']' FROM (SELECT DISTINCT(MONTH) FROM #TEMPRESULT_TOTAL) AS T1/*生成select后的列名*/
23 SELECT @STR2=@STR2+',['+MONTH+']' FROM (SELECT DISTINCT(MONTH) FROM #TEMPRESULT_TOTAL ) AS T2
24 SET @STR=RIGHT(@STR,LEN(@STR)-1)
25 SET @STR2=RIGHT(@STR2,LEN(@STR2)-1)
26 SET @SQL=@SQL+@STR+' FROM #TEMPRESULT_TOTAL PIVOT (MAX(Divide) FOR MONTH IN ('+@STR2+')) AS T '
27 EXEC(@SQL)
结果
以上转换的前提是原始table数据列只有一行DIVIDE
但遇到如下table时【MONTH对应多个数据列】，需转换两次
第一步：增加RESULT列，把原列名以ITEM列的数据形式存在
SELECT MONTH,ITEM,RESULT
--INTO #TEMPRESULT2
FROM #TEMPRESULT
UNPIVOT /*列转行*/
(
RESULT FOR ITEM IN (SUM,CUT,COST)
) AS UNPVT
结果
然后就类似前面提到的操作
--再將月份從行轉成列
DECLARE @STR NVARCHAR(MAX)
DECLARE @STR2 NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
SET @STR=''
SET @STR2=''
SET @SQL='SELECT ITEM,'
SELECT @STR=@STR+',ISNULL(['+MONTH+'],0) AS ['+MONTH+']' FROM (SELECT MONTH FROM #TEMPRESULT) AS T1/*生成select后的列名*/
SELECT @STR2=@STR2+',['+MONTH+']' FROM (SELECT MONTH FROM #TEMPRESULT ) AS T2
SET @STR=RIGHT(@STR,LEN(@STR)-1)
SET @STR2=RIGHT(@STR2,LEN(@STR2)-1)
SET @SQL=@SQL+@STR+' FROM #TEMPRESULT2 PIVOT (SUM(RESULT) FOR MONTH IN ('+@STR2+')) AS T '
EXEC(@SQL)
转换两次后的结果
