SqlServer行转列方法

数据库建表

createtable student 
(id intidentity(1,1)primarykey,
name nvarchar(20),
subject_id int,
score int,
grade varchar(10))insertinto student(name,subject_id,score,grade)values('张三',1,90,'A')insertinto student(name,subject_id,score,grade)values('张三',2,75,'B')insertinto student(name,subject_id,score,grade)values('李四',1,70,'B')insertinto student(name,subject_id,score,grade)values('李四',2,88,'B')insertinto student(name,subject_id,score,grade)values('王五',1,65,'C')insertinto student(name,subject_id,score,grade)values('王五',2,65,'C')select*from student

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

select name,max(casewhen subject_id=1then score else0end)as Chinese,max(casewhen subject_id=2then score else0end)as math
from student 
groupby 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
groupby name

3.行转列，多列，参考

--1select name,max(casewhen subject_id=1then score else0end)as Chinese,max(casewhen subject_id=1then grade elsenullend)as Chinese_g,max(casewhen subject_id=2then score else0end)as math,max(casewhen subject_id=2then grade elsenullend)as math_g
from student 
groupby name;--2with A as(select name,max([1])as Chinese,max([2])as math
from student  
pivot(max(score)for subject_id in([1],[2]))t
groupby 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
groupby name)select a.name,Chinese,Chinese_g,math,math_g from  A,B where A.name=b.name 
--3select name,Chinese,max([1])as Chinese_g,math,max([2])as math_g
    from student 
    innerjoin(select name as namea,max([1])as Chinese,max([2])as math from student  
    pivot(max(score)for subject_id in([1],[2]))t 
    groupby name) st on student.name=st.namea
    pivot(max(grade)for subject_id in([1],[2]))ss
 groupby name,Chinese,math