【SQL基础用法】——行列转换

*


标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

--------------
问题：假设有张学生成绩表(tb)如下:

姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94

想变成(得到如下结果)： 
姓名 语文 数学 物理 
-- ---- ---- ----
李四 74 84 94
张三 74 83 93
--
-----------------
*/
create table tb
(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb 
values('张三' , '语文' , 74)
insert into tb values
('张三' , '数学' , 83)

insert
 
into
 tb 
values
(
'
张三
'
 , 
'
物理
'
 , 
93
)


insert
 
into
 tb 
values
(
'
李四
'
 , 
'
语文
'
 , 
74
)


insert
 
into
 tb 
values
(
'
李四
'
 , 
'
数学
'
 , 
84
)


insert
 
into
 tb 
values
(
'
李四
'
 , 
'
物理
'
 , 
94
)


go





--
SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)



select
 姓名 
as
 姓名 ,

  
max
(
case
 课程 
when
 
'
语文
'
 
then
 分数 
else
 
0
 
end
) 语文,

  
max
(
case
 课程 
when
 
'
数学
'
 
then
 分数 
else
 
0
 
end
) 数学,

  
max
(
case
 课程 
when
 
'
物理
'
 
then
 分数 
else
 
0
 
end
) 物理


from
 tb


group
 
by
 姓名




--
SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)



declare
 
@sql
 
varchar
(
8000
)


set
 
@sql
 
=
 
'
select 姓名 
'



select
 
@sql
 
=
 
@sql
 
+
 
'
 , max(case 课程 when 
'''
 
+
 课程 
+
 
'''
 then 分数 else 0 end) [
'
 
+
 课程 
+
 
'
]
'



from
 (
select
 
distinct
 课程 
from
 tb) 
as
 a


set
 
@sql
 
=
 
@sql
 
+
 
'
 from tb group by 姓名
'



exec
(
@sql
) 




--
SQL SERVER 2005 静态SQL。



select
 
*
 
from
 (
select
 
*
 
from
 tb) a pivot (
max
(分数) 
for
 课程 
in
 (语文,数学,物理)) b




--
SQL SERVER 2005 动态SQL。



declare
 
@sql
 
varchar
(
8000
)


select
 
@sql
 
=
 
isnull
(
@sql
 
+
 
'
],[
'
 , 
''
) 
+
 课程 
from
 tb 
group
 
by
 课程


set
 
@sql
 
=
 
'
[
'
 
+
 
@sql
 
+
 
'
]
'



exec
 (
'
select * from (select * from tb) a pivot (max(分数) for 课程 in (
'
 
+
 
@sql
 
+
 
'
)) b
'
)




--
-------------------------------