我有一个表 分类1 分类2 分类3 个数
a1 a2 急 5
a1 a2 慢 2
a1 b2 急 1
a1 b2 慢 9
b1 c2 急 2
b1 c2 慢 3
怎样得到这样的报表格式呢?
分类1 分类2 急 慢
a1 a2 5 2
a1 b2 1 9
b1 c2 2 3
a1 a2 急 5
a1 a2 慢 2
a1 b2 急 1
a1 b2 慢 9
b1 c2 急 2
b1 c2 慢 3
怎样得到这样的报表格式呢?
分类1 分类2 急 慢
a1 a2 5 2
a1 b2 1 9
b1 c2 2 3
select 分类1, 分类2, sum(case 分类3 when '急' then 个数 else 0 end) as 急, sum(case 分类3 when '慢' then 个数 else 0 end) as 慢 from Table1 group by 分类1, 分类2
----建测试数据表
create table ab([分类1] varchar(10),[分类2] varchar(10),[分类3] varchar(10),[个数] int)
insert into ab
select 'a1','a2','急',5
union all select 'a1','a2','慢',2
union all select 'a1','b2','急',1
union all select 'a1','b2','慢',9
union all select 'b1','c2','急',2
union all select 'b1','c2','慢',3 ---提取数据
select [分类1],[分类2],sum([急]) as 急,sum([慢]) as 慢 from(
select [分类1],[分类2],sum([个数]) as 急 ,0 as 慢 from ab where [分类3]='急' GROUP BY [分类1],[分类2]
union all
select [分类1],[分类2],0 as 急,sum([个数]) as 慢 from ab where [分类3]='慢' GROUP BY [分类1],[分类2])
m GROUP BY [分类1],[分类2]
---结果
a1 a2 5 2
a1 b2 1 9
b1 c2 2 3