欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-26 14:39:58
/*创建数据库*/ create database tmp go use tmp go /*创建数据库测试表*/ create...

/*创建数据库*/
 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