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 )

转换两次后的结果

转载于:https://www.cnblogs.com/songye/archive/2011/04/01/2002238.html