表:t1
列:name,type,score
其中一个name有多个type的score,name也有重的,type值是有限的几个数据
我想显示成 type1 type2 ... typen
name score scorename不能重复
列:name,type,score
其中一个name有多个type的score,name也有重的,type值是有限的几个数据
我想显示成 type1 type2 ... typen
name score scorename不能重复
sum(case when type='1' then type else ''end) typ1,
sum(case when type='2' then type else ''end) typ2
..
from tb
gruop by name有限的有这种,不限制的用动态
declare @t table(name varchar(10),type varchar(10),score int);
insert into @t select '小王','A',100 union all
select '小明','A',98 union all
select '小王','B',90 union all
select '小张','C',90;--select * from @t;select name ,[A] as [A],[B] as [B], [C] as [C] from @t
pivot (sum(score) for type in ([a],[b],[c])) pvt
declare @t table
(name varchar(1),type varchar(2),score int)
insert into @t
select 'a','t1',89 union all
select 'a','t2',19 union all
select 'a','t3',10 union all
select 'b','t1',12 union all
select 'b','t2',18 union all
select 'b','t3',14 union all
select 'c','t1',12 union all
select 'c','t2',11select name,
t1=sum(case type when 't1' then score else 0 end),
t2=sum(case type when 't2' then score else 0 end),
t3=sum(case type when 't3' then score else 0 end)
from @t group by name
/*
name t1 t2 t3
---- ----------- ----------- -----------
a 89 19 10
b 12 18 14
c 12 11 0
*/
name,
sum(case when type='1' then type else ''end) type1,
sum(case when type='2' then type else ''end) type2
..
sum(case when type='n' then type else ''end) typen
from
tb
gruop by
name
create table #t1
([name] nvarchar(10),[type] nvarchar(5),score float)
insert #t1
select 'AAA','1',98.5 union all
select 'AAA','2',54.7 union all
select 'AAA','3',87.7 union all
select 'AAA','4',98.6 union all
select 'AAA','5',100 union all
select 'BBB','2',57.6 union all
select 'BBB','4',68.3 union all
select 'BBB','5',77.8 declare @sql as nvarchar(4000)
set @sql='select [name]'
select @sql=@sql+',max(case [type] when '''+[type]+''' then score else '''' end) as '''+[type]+''''
from (select distinct [type] from #t1)as t
set @sql=@sql+' from #t1 group by [name]'
exec(@sql)
insert into @t select '小王','A',100 union all
select '小明','A',98 union all
select '小王','B',90 union all
select '小张','C',90;--select * from @t;select name ,[A] as [A],[B] as [B], [C] as [C] from @t
pivot (sum(score) for type in ([a],[b],[c])) pvt