SQL行转列

　　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行转列

标签：else   log   sel   实现   使用   core   数学   信息   font