首页 > 代码库 > SQL行转列

SQL行转列

一、使用场景

  当系统中有汇总和明细的需求时,一般通过SQL来实现,汇总 就是 根据条件显示出全部的数据,明细 就是 一条汇总对应的详细信息。

  行转列通常用于实现明细的时候。

二、举例实现

  1.当表中不存在id时:

  1). 新建表student,姓名(name)、学科(subject)、成绩(score)

create table student(name nvarchar(20),subject nvarchar(20),score int)

  2). 插入数据

insert into student values(张三,语文,80),(张三,数学,90),(张三,英语,75) insert into student values(李四,语文,75),(李四,数学,93),(李四,英语,56) insert into student values(王五,语文,87),(王五,数学,67),(王五,英语,83) 

  3). 方式一实现:使用case when ... then ... end

select name,    max(case subject when 语文 then score else 0 end) 语文,    max(case subject when 数学 then score else 0 end) 数学,    max(case subject when 英语 then score else 0 end) 英语,    SUM(score)  as 总分,    cast(AVG(cast(score as decimal(10,2))) as decimal(10,2)) as 平均分from student group by name

  技术分享

  4) 方式二实现:自己和自己关联

select     a.name,a.score,b.score,c.score,(a.score+b.score+c.score) 总分,CAST((a.score+b.score+c.score)/3.0 as decimal(5,2)) 平均分 from student a,student b,student c where a.name = b.name and b.name = c.name and a.subject=语文 and b.subject=数学 and c.subject=英语

  技术分享

  5) 方式三实现:使用pivot

select * from student pivot(max(score) for subject in (语文,数学,英语))a 

   技术分享

  2.当表中存在id时,

  1). 新建表tb_student,序号(id)、姓名(name)、学科(subject)、成绩(score)

create table tab_student(id int,name nvarchar(20),subject nvarchar(20),score int)

  2). 插入数据:

insert into tab_student values (1,张三,语文,76),(2,张三,数学,85),(3,张三,英语,67)insert into tab_student values (3,李四,语文,87),(4,李四,数学,32),(5,李四,英语,85)insert into tab_student values (6,王五,语文,83),(7,王五,数学,90),(8,王五,英语,80)

  3) 方式一实现:使用 case when 

select name,MAX(case subject when 语文 then score else 0 end) as 语文,       MAX(case subject when 数学 then score else 0 end) as  数学,       MAX(case subject when 英语 then score else 0 end) as  英语,       SUM(score) 总数,       cast(AVG(CAST(score as decimal(10,2))) as decimal(10,2)) 平均数 from tab_student group by name

  技术分享

  4). 方式二实现:自己关联自己

select     a.name,a.score,b.score,c.score,(a.score+b.score+c.score) 总分,CAST((a.score+b.score+c.score)/3.0 as decimal(5,2)) 平均分 from tab_student a,tab_student b,tab_student c where a.name = b.name and b.name = c.name and a.subject=语文 and b.subject=数学 and c.subject=英语

   技术分享

   5). 方式三实现不了:pivot

select * from tab_student pivot(    max(score) for subject in (语文,数学,英语))a 

   技术分享

 

SQL行转列