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
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
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
) T
ORDER BY [区域],[月份]
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
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
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
) T
ORDER 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 [区域],[月份]
create table tb
(区域 varchar(10),月份 varchar(10),工资 int,福利 int,奖金 int)insert into tb
select 'A','1月',100,200,300 union all
select 'A','2月',110,210,310 union all
select 'A','3月',120,220,320 union all
select 'B','1月',1100,1200,1300 union all
select 'B','2月',1010,2010,3010 union all
select 'B','3月',1300,2300,3300
declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','')
+'max(case when 月份='''+月份+''' then v else 0 end) '''+月份+''' '
from (select distinct 月份 from tb) tselect @tsql='select 区域,cast(c as varchar(15)) ''月份'','+@tsql
+' from (select 区域,月份,c,v
from tb a
unpivot(v for c in([工资],[福利],[奖金])) u) p
group by 区域,c
order by 区域,case c when ''工资'' then 1
when ''福利'' then 2
when ''奖金'' then 3 end 'exec(@tsql)/*
区域 月份 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(6 row(s) affected)
*/
已结贴,不好用意思
+'max(case when 月份='''+月份+''' then v else 0 end) '''+月份+''' ' 这里的max 可用 sum? v 这个字段是指哪个