表结构如下
name type num
china a 10
china b 15
chian c 20
usa a 25
usa b 30
usa c 40写查询语句实现下面这种效果
a b c
china 10 15 20
usa 25 30 40
name type num
china a 10
china b 15
chian c 20
usa a 25
usa b 30
usa c 40写查询语句实现下面这种效果
a b c
china 10 15 20
usa 25 30 40
,sum(case when type = 'b' then num else 0 end as b
,sum(case when type = 'c' then num else 0 end as c
from t
group by name
insert test select 'china','a',10
union all select 'china','b',15
union all select 'china','c',20
union all select 'usa','a',25
union all select 'usa','b',30
union all select 'usa','c',40select name,
a=sum(case type when 'a' then num else 0 end),
b=sum(case type when 'b' then num else 0 end),
c=sum(case type when 'c' then num else 0 end)
from test
group by name
select name,sum(case when type = 'a' then num else 0 end as a
,sum(case when type = 'b' then num else 0 end as b
,sum(case when type = 'c' then num else 0 end as c
from t
group by name
insert test select 'china','a',10
union all select 'china','b',15
union all select 'china','c',20
union all select 'usa','a',25
union all select 'usa','b',30
union all select 'usa','c',40
/*
testname type num
------------------------------
china a 10
china b 15
china c 20
usa a 25
usa b 30
usa c 40
*/declare @exec varchar(8000)
set @exec='select name'
select @exec=@exec+',max(case when type='''+type+''' then num end) as '''+type+'''' from (select type from test group by type) a
set @exec=@exec+'from test group by name '
exec (@exec)/*
name a b c
--------------------------------
china 10 15 20
usa 25 30 40
*/