SQL Server 行转列 PIVOT 用法及解释

下面介绍Sql server 中常用的行转列操作；

1. 语法

PIVOT用于将列值旋转为列名（即行转列），在SQL 2005以前可以使用case when then...语句，但这种方法的问题在于列举的列名要写死，如果列名很多，case when 语句会很长，并不优雅。在2005版本就推出了 pivot 关键字，可以方便的实现。

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 IN (….....) ) AS P

完整语法：

PIVOT(<聚合函数>([聚合列名]) FOR [行转列前的列名] IN ([行转列后的列名1],[行转列后的列名2],[行转列后的列名3],.......[行转列后的列名N]))

2. 聚合函数

3. 举例

2.1 创建一张Student 表：​​​​​​

CREATE TABLE Student(
  [Name] [nvarchar](50) NULL,
  [Sex] [nvarchar](10) NULL,
  [Age] [nvarchar](50) NULL,
  [Grade] [nvarchar](50) NULL,
  [Class] [nvarchar](50) NULL,
  [Score] [decimal](18, 2) NULL,
)

2.2 插入数据

INSERT Student (Name,Sex,Age,Grade,Class,Score)
VALUES
('小红','女','12','5','数学','90'),
('小红','女','12','5','语文','89'),
('小红','女','12','5','英语','95'),
('小蓝','男','13','5','数学','93'),
('小蓝','男','13','5','语文','87'),
('小蓝','男','13','5','英语','92'),
('小花','女','11','5','英语','91'),
('小花','女','11','5','数学','85'),
('小花','女','11','5','语文','92')

2.3 按行查询学生名字，列举出 数学，语文，英语成绩

方法1：使用 PIVOT

SELECT * FROM (SELECT Name,Class,Score FROM Student) A
PIVOT (MAX(Score) FOR Class IN ([数学],[语文],[英语])) as B

方法2：使用CASE​​​​​​​

SELECT 
Name,
MAX(CASE WHEN Class='数学' THEN Score ELSE 0 END) AS [数学],
MAX(CASE WHEN Class='语文' THEN Score ELSE 0 END) AS [语文],
MAX(CASE WHEN Class='英语' THEN Score ELSE 0 END) AS [英语]
FROM Student
GROUP BY NAME

附加：

查询字段名和备注

SELECT c.name AS ColumnName, ep.value AS Comment
FROM sys.columns c
LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE c.object_id = OBJECT_ID('表名')

 修改表字段注释

EXEC sys.sp_updateextendedproperty
@name = N'MS_Description',
@value = N'注释描述',
@level0type = N'SCHEMA',
@level0name = 'dbo',
@level1type = N'TABLE',
@level1name = '表名',
@level2type = N'COLUMN',
@level2name = '列名';

4. pivot 动态行转列

declare @name nvarchar(4000),
@strSql nvarchar(max)
 
SET @name=''
--赋值 把所有要转化为列的数据保存在字符串中，并且以逗号分隔
select @name=@name+CDate from (select distinct '['+Class+'],' AS CDate FROM Student group by Class) AS t

--去掉末尾的一个逗号
SET @name=SUBSTRING(@name,1,LEN(@name)-1)

--打印
PRINT(@name)

SET @strSql ='select b.Name as 姓名, b.Sex as 性别, '+@Name+'
    from Student 
    pivot(
        MAX(Score) 
        for Class 
        in ('+@Name+')
    )  
    as b
    '
--打印最终执行的SQL
PRINT(@strSql)
--执行sql
EXEC (@strSql)