sqlÐÐÁл¥×ª(תÌû)

--ÐÐÁл¥×ª
/******************************************************************************************************************************************************
ÒÔѧÉú³É¼¨ÎªÀý×Ó£¬±È½ÏÐÎÏóÒ׶®
ÕûÀíÈË£ºÖйú·ç(Roy)
ÈÕÆÚ:2008.06.06
******************************************************************************************************************************************************/
--1¡¢Ðл¥ÁÐ
--> --> (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
--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 when [Course]='ÓïÎÄ' then [Score] else 0 end)
from
Class
group by [Student]
GO
--2005·½·¨:
¶¯Ì¬:
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