select distinct cityname as [name], yidong=(select conunt from test_a where cityname=c.cityname and test='移动'), liangtong=(select conunt from test_a where cityname=c.cityname and test= '连通') from test_a as c
create table t(地区 varchar(10),类型 varchar(10),count int) insert into t select '苏州','移动',300 insert into t select '苏州','连通',600 declare @sql varchar(8000) set @sql='select 地区' select @sql=@sql+',['+类型+']=sum(case 类型 when '''+类型+''' then count else 0 end)' from (select distinct 类型 from t)a exec(@sql+' from t group by 地区')
yidong=(select conunt from test_a where cityname=c.cityname and test='移动'),
liangtong=(select conunt from test_a where cityname=c.cityname and test= '连通')
from test_a as c
create table t(地区 varchar(10),类型 varchar(10),count int)
insert into t select '苏州','移动',300
insert into t select '苏州','连通',600
declare @sql varchar(8000)
set @sql='select 地区'
select @sql=@sql+',['+类型+']=sum(case 类型 when '''+类型+''' then count else 0 end)'
from (select distinct 类型 from t)a
exec(@sql+' from t group by 地区')