行列互换问题
数据库中tb表格如下
区域 月份 工资 福利 奖金
A 1月 100 200 300
A 2月 110 210 310
A 3月 120 220 320
B 1月 1100 1200 1300
B 2月 1010 2010 3010
B 3月 1300 2300 3300
转化成如下结果：
区域 月份 1月 2月 3月
A 工资 100 110 120
A 福利 200 210 220
A 奖金 300 310 320
B 工资 1100 1010 1300
B 福利 1200 2010 2300
B 奖金 1300 3010 3300
[解决办法]
忘加GROUP BY了，用这个：
SELECT *
FROM (
SELECT [区域]
,'工资' AS [月份]
,SUM(CASE WHEN '月份'='1月' THEN [工资] ELSE 0 END) AS [1月]
,SUM(CASE WHEN '月份'='2月' THEN [工资] ELSE 0 END) AS [2月]
,SUM(CASE WHEN '月份'='3月' THEN [工资] ELSE 0 END) AS [3月]
FROM TB
GROUP BY [区域]
UNION ALL
SELECT [区域]
,'福利' AS [月份]
,SUM(CASE WHEN '月份'='1月' THEN [福利] ELSE 0 END) AS [1月]
,SUM(CASE WHEN '月份'='2月' THEN [福利] ELSE 0 END) AS [2月]
,SUM(CASE WHEN '月份'='3月' THEN [福利] ELSE 0 END) AS [3月]
FROM TB
GROUP BY [区域]
UNION ALL
SELECT [区域]
,'奖金' AS [月份]
,SUM(CASE WHEN '月份'='1月' THEN [奖金] ELSE 0 END) AS [1月]
,SUM(CASE WHEN '月份'='2月' THEN [奖金] ELSE 0 END) AS [2月]
,SUM(CASE WHEN '月份'='3月' THEN [奖金] ELSE 0 END) AS [3月]
FROM TB
GROUP BY [区域]
) T
ORDER BY [区域],[月份]
