34,590
社区成员
发帖
与我相关
我的任务
分享
select units 名称,
sum(case when flag='标志1' and kind='A' then [Money] else 0 end) A1,
sum(case when flag='标志1' and kind='B' then [Money] else 0 end) B1,
sum(case when flag='标志2' and kind='A' then [Money] else 0 end) A2,
sum(case when flag='标志2' and kind='B' then [Money] else 0 end) B2,
sum(case when flag='标志3' and kind='A' then [Money] else 0 end) A3,
sum(case when flag='标志3' and kind='B' then [Money] else 0 end) B3
from 表
group by units;
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (units nvarchar(6),flag nvarchar(6),kind nvarchar(2),Money int)
insert into [TB]
select '名称1','标志1','A',10 union all
select '名称1','标志2','B',23 union all
select '名称2','标志3','A',4 union all
select '名称2','标志2','A',6 union all
select '名称3','标志3','A',77 union all
select '名称3','标志3','B',6
select * from [TB]
SELECT units AS '名称',
SUM(CASE WHEN flag='标志1' AND kind = 'A' THEN money ELSE 0 END) AS '标志1_A',
SUM(CASE WHEN flag='标志1' AND kind = 'B' THEN money ELSE 0 END) AS '标志1_B',
SUM(CASE WHEN flag='标志2' AND kind = 'A' THEN money ELSE 0 END) AS '标志2_A',
SUM(CASE WHEN flag='标志2' AND kind = 'B' THEN money ELSE 0 END) AS '标志2_B',
SUM(CASE WHEN flag='标志3' AND kind = 'A' THEN money ELSE 0 END) AS '标志3_A',
SUM(CASE WHEN flag='标志3' AND kind = 'B' THEN money ELSE 0 END) AS '标志3_B'
FROM dbo.TB
GROUP BY units
/*
名称 标志1_A 标志1_B 标志2_A 标志2_B 标志3_A 标志3_B
------ ----------- ----------- ----------- ----------- ----------- -----------
名称1 10 0 0 23 0 0
名称2 0 0 6 0 4 0
名称3 0 0 0 0 77 6
(3 行受影响)
*/