SQL server2005ÖÐÓÃpivotʵÏÖÐÐÁÐת»»

 --> --> (Roy)Éú³É²âÊÔÊý¾Ý
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'ÕÅÈý',N'ÓïÎÄ',78 union all
select N'ÕÅÈý',N'Êýѧ',87 union all
select N'ÕÅÈý',N'Ó¢Óï',82 union all
select N'ÕÅÈý',N'ÎïÀí',90 union all
select N'ÀîËÄ',N'ÓïÎÄ',65 union all
select N'ÀîËÄ',N'Êýѧ',77 union all
select N'ÀîËÄ',N'Ó¢Óï',65 union all
select N'ÀîËÄ',N'ÎïÀí',85
Go
¶¯Ì¬:
declare @s nvarchar(4000)
Select  @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
Éú³É¾²Ì¬:
select *
from
Class
pivot
(max([Score]) for [Course] in([Êýѧ],[ÎïÀí],[Ó¢Óï],[ÓïÎÄ]))b
Éú³É¸ñʽ:
/*
Student Êýѧ          ÎïÀí          Ó¢Óï          ÓïÎÄ
------- ----------- ----------- ----------- -----------
ÀîËÄ      77          85          65          65
ÕÅÈý      87          90          82          78
(2 ÐÐÊÜÓ°Ïì)
*/
--2000·½·¨:
¶¯Ì¬:
declare @s nvarchar(4000)
set @s=''
Select  @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
Éú³É¾²Ì¬:
select
[Student],
[Êýѧ]=max(case when [Course]='Êýѧ' then [Score] else 0 end),
[ÎïÀí]=max(case when [Course]='ÎïÀí' then [Score] else 0 end),
[Ó¢Óï]=max(case when [Course]='Ó¢Óï' then [Score] else 0 end),
[ÓïÎÄ]=max(case whe