Sqlserver行转列pivot()

PIVOT概述：

PIVOT用于将列值旋转为列名（即行转列），并在必要时对最终输出中所需的任何其余列值执行聚合。

PIVOT的一般语法：
SELECT [新表字段1，2，3…] FROM [原表名]
AS [原表别名]
PIVOT( [聚合函数] ( [原表字段1] ) FOR [原表字段2] IN ( [原表2值1]，[原表字段2值2]… ) ) AS [新表别名]

语法解释：
1、PIVOT必须列举[原表字段2的值]，列举的值必须用中括号 [ ] 包含起来，就算是字符串类型也不需要单引号 ’ ’
2、PIVOT中列举的值将作为新表的字段名称
3、为什么会有聚合函数？此处并没有GROUP BY 呀！偷偷告诉你，GROUP BY 是隐藏的，除了语句中出现的两个 [原表字段]，其他[原表字段]将被GROUP BY，这样才使得上面的PIVOT结果出现多行
4、列举字段的这个组在原表中没有数据将以NULL值存在于PIVOT后的新表
5、PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为pivot后的新表

CREATE TABLE [dbo].[StuInfo](
	[studentname] [varchar](30) NULL,
	[subject] [varchar](10) NULL,
	[grade] [int] NULL
) ON [PRIMARY]

//插入数据
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('刘备', '语文', 80);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('刘备', '数学', 82);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('刘备', '英语', 84);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('赵云', '语文', 70);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('赵云', '数学', 74);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('赵云', '英语', 76);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('关羽', '语文', 90);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('关羽', '数学', 93);
INSERT INTO [dbo].[StuInfo]([studentname], [subject], [grade]) VALUES ('关羽', '英语', 94);

select * from  StuInfo

需求：按学生名称查询各科成绩：

静态case when 实现

select studentname,
	sum(case when subject='语文' then grade else NULL end) as 语文,
	sum(case when subject='数学' then grade else NULL end) as 数学,
	sum(case when subject='英语' then grade else NULL end) as 英语
from stuinfo
group by studentname

结果如下：

静态pivot

select * from  stuinfo pivot(sum(grade) for subject in ([语文],[数学],[英语]) ) as P 

如果每位同学都增加了物理成绩，静态语句需要做调整，这个时候可以用动态查询。

insert into StuInfo values('关羽','物理',100);
insert into StuInfo values('刘备','物理',90);
insert into StuInfo values('赵云','物理',80);

–动态case when

declare @SQL nvarchar(max)
declare @column nvarchar(max)

set @column=N'';

with sub as
(
select distinct subject  from stuinfo
)
select @column+=N'sum(case when subject='''+ subject + N''' then grade else null end )as '+ subject + N','
from sub 
select @column=SUBSTRING(@column,1,len(@column)-1)
select @SQL=N'select studentname,'+@column+N' from stuinfo
group by stuinfo.studentname'
print(@sql)
exec(@SQL)

–动态pivot

declare @sql nvarchar(max)
declare @column nvarchar(max)

set @column=N'';

with sub AS
(
select distinct subject
from stuinfo
)

select @column+=N'[' + cast(subject as varchar(30)) + N'],'
from sub  

select @column=SUBSTRING(@column,1,len(@column)-1)
select @sql=N'select pivot_stuinfo.studentname, ' + @column + 
N' from stuinfo pivot(sum(grade) for subject in (' + @column + N')) as pivot_stuinfo'

print(@sql)
exec (@sql)

结果为：