34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
区域 varchar(10),
厂家 varchar(10)
)
go
insert into tb
select '龙泉','诺西' union all
select '高新','华为' union all
select '高新','诺西' union all
select '高新','诺西' union all
select '高新','诺西'
go
declare @str varchar(max)
select @str=isnull(@str+',','')+'['+厂家+']=sum(case when 厂家='''+厂家+''' then 1 else 0 end)' from (select distinct 厂家 from tb) t1
exec('select 区域,'+@str+' from tb group by 区域')
go
/*
区域 华为 诺西
---------- ----------- -----------
高新 1 3
龙泉 0 1
(2 行受影响)
*/
select 区域,sum(case when 厂家='华为' then 1 else 0 end) [华为],
sum(case when 厂家='诺西' then 1 else 0 end) [诺西]
from tb
group by 区域
select
区域,
sum(case 厂家 when '诺西' then 1 else 0 end) as 诺西,
sum(case 厂家 when '华为' then 1 else 0 end) as 华为
from
tb
group by
区域
select 区域,诺西=sum(case when 厂家='诺西' then 1 else 0 end),
华为=sum(case when 厂家='华为' then 1 else 0 end)
from tb group by 区域