表格如下:
ID NAME
001 A
001 B
001 C
002 A
002 B
002 C
003 A
003 B
003 C
要求查询得出:
ID TYPE1 TYPE2 TYPE3
001 A B C
002 A B C
003 A B C
解决方案:
create table test(ID char(3), NAME char(10))
insert into test
select
'001' , 'A'
union select
'001' , 'B'
union select
'001' , 'C'
union select
'002' , 'A'
union select
'002' , 'B'
union select
'002' , 'C'
union select
'003' ,'A'
union select
'003' , 'B'
union select
'003' , 'C'
select * from test
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT id'
SELECT @SQL= @SQL+ ',max(CASE WHEN name = ''' + name + ''' THEN name END) [Type'+name+']' FROM (SELECT DISTINCT name FROM test) A
SET @SQL=@SQL+ ' FROM test GROUP BY id'
EXEC (@SQL)
drop table test