浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用

/*创建数据库*/
 create database tmp
 go
 use tmp
 go

/*创建数据库测试表*/
create table [scores]
     (
       [id] int identity(1, 1)
                primary key ,
       [student] varchar(20) ,
       [subject] varchar(30) ,
       [score] float
     )

go

truncate table scores
 /*插入数据库测试数据信息*/
  insert  into scores
         ( student, subject, score )
 values  ( 'test001', '语文', '90' )
 insert  into scores
         ( student, subject, score )
 values  ( 'test001', '英语', '85' )
 insert  into scores
         ( student, subject, score )
 values  ( 'text002', '语文', '90' )
 insert  into scores
         ( student, subject, score )
 values  ( 'text002', '英语', '80' )
 insert  into scores
         ( student, subject, score )
 values  ( 'test003', '语文', '95' )
 insert  into scores
         ( student, subject, score )
 values  ( 'test003', '英语', '85' )

/*1.  case when .......then else  ....end 用法，行列转换*/
 select  student as '姓名' ,
         max(case subject
               when '语文' then score
               else 0
             end) as '语文' ,--如果这个行是“语文”，就选此行作为列
        max(case subject
               when '英语' then score
               else 0
             end) as '英语'
from    scores
 group by student
 order by student

/*2. pivot(聚合函数(要转成列值的列名)
　      for 要转换的列
 　　    in（目标列名）
 　　)*/

select  student as '姓名' ,
         avg(语文) as '语文' ,
         avg(英语) as '英语'
from    scores pivot( avg(score) for subject in ( 语文, 英语 ) )as newscores
 group by student
 order by student asc