SQL 行转列

初始化数据

 

select * into #temp from 
(
select Name = 'aaa', Course = 'English', Score = 95 union all
select Name = 'aaa', Course = 'Chinese', Score = 82 union all
select Name = 'aaa', Course = 'History', Score = 77 union all
select Name = 'bbb', Course = 'History', Score = 92 union all
select Name = 'bbb', Course = 'English', Score = 66 union all
select Name = 'bbb', Course = 'Chinese', Score = 100
) as tb

方法一，使用聚合函数，SUM，MAX，MIN等都一样
-- Line to Column

select Name, 
    Max(case when Course = 'English' then Score end) as 'English',
    Max(case when Course = 'Chinese' then Score end) as 'Chinese',
    Max(case when Course = 'History' then Score end) as 'History'
    from #temp
    group by Name

方法二， 使用Pivot翻转
-- Use Pivot

select 
    *
from
    #temp
pivot
(
    max(Score)
    for Course
    in ([English], [Chinese], [History])
) b

 

Refer to 

http://hi.baidu.com/xg21/blog/item/271805347872f13a5ab5f57a.html