【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


'


)










--


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