SQL 動態合併

Create table T( id int, type varchar(10), value int)

Go

insert into T

select  1,'A' ,10  union all

select  1,'B' ,20  union all

select  1,'C' ,30  union all

select  2,'A' ,15  union all

select  2,'B' ,20  union all

select  2,'D' ,30  union all

select  3,'B' ,15  union all

select  4,'A' ,10  union all

select  4,'C' ,20 union all

select 5,'Z',40

 

GO

select * from T

 

GO

select id,

max(case when type='A' then value else 0 end) as A,

max(case when type='B' then value else 0 end) as B,

max(case when type='C' then value else 0 end) as C,

max(case when type='D' then value else 0 end) as D

from T

group by id

 

GO

declare @sql varchar(8000)

set @sql=''

select @sql=@sql+',max(case when type='''+type+''' then value else 0 end ) as '+type

from T

group by type

 

select @sql='select id'+@sql+' from T group by id'

exec(@sql)

 

GO

drop table T

GO