【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

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

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

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