表格行变列

表格如下：
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