sql语句行列转换的两种方法 case...when和pivot函数应用，运用pivot 函数只支持数据库版本2005以上的。一般运用case when else end 的方法比较多，比较普遍 /*创建数据库*/ create database tmp go use tmp go /*创建数据库测试表*/ create table [scores] (
sql语句行列转换的两种方法 case...when和pivot函数应用，运用pivot 函数只支持数据库版本2005以上的。一般运用case when else end 的方法比较多，，比较普遍
/*创建数据库*/
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