SqlServer行转列方法-Toy模板网

这篇具有很好参考价值的文章主要介绍了SqlServer行转列方法。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

数据库建表

create table student 
(id int identity(1,1) primary key,
name nvarchar(20),
subject_id int,
score int,
grade varchar(10)
)

insert into student(name,subject_id,score,grade)values('张三',1,90,'A')
insert into student(name,subject_id,score,grade)values('张三',2,75,'B')
insert into student(name,subject_id,score,grade)values('李四',1,70,'B')
insert into student(name,subject_id,score,grade)values('李四',2,88,'B')
insert into student(name,subject_id,score,grade)values('王五',1,65,'C')
insert into student(name,subject_id,score,grade)values('王五',2,65,'C')

select * from student

sqlserver行转列,# DB_SqlServer,sqlserver,数据库,sql

1 使用聚合函数巧妙解决，可以用max、sum等

select name,
	max(case when subject_id=1 then score else 0 end) as Chinese,
	max(case when subject_id=2 then score else 0 end) as math
from student 
group by name

sqlserver行转列,# DB_SqlServer,sqlserver,数据库,sql

2 使用pivot函数

select name,
	max([1]) as Chinese,
	max([2]) as math
from student  pivot(max(score) for subject_id in ([1],[2]))t
group by name

sqlserver行转列,# DB_SqlServer,sqlserver,数据库,sql
3.行转列，多列，参考

--1
select name,
	max(case when subject_id=1 then score else 0 end) as Chinese,
	max(case when subject_id=1 then grade else null end) as Chinese_g,
	max(case when subject_id=2 then score else 0 end) as math,
    max(case when subject_id=2 then grade else null end) as math_g
from student 
group by name;
--2
with A as 
(select name,
	max([1]) as Chinese,
	max([2]) as math
from student  
pivot(max(score) for subject_id in ([1],[2])
)t
group by name),
B as(
select name,
	max([1]) as Chinese_g,
	max([2]) as math_g
from student  
pivot(max(grade) for subject_id in ([1],[2])
)t
group by name)
select a.name,Chinese,Chinese_g,math,math_g from  A,B where A.name=b.name 
--3
select name,Chinese,max([1]) as Chinese_g,math,max([2]) as math_g
	from student 
	inner join 
	(select name as namea,max([1]) as Chinese,max([2]) as math from student  
	pivot(max(score) for subject_id in ([1],[2]))t 
	group by name) st on student.name=st.namea
	pivot(max(grade) for subject_id in ([1],[2]))ss
 group by name,Chinese,math