SQL行转列、列转行（SQL Server版）

在SQL Server中使用SQL实现行转列、列转行，可以使用多种方法，在SQL 2005以前可以使用case when then...语句，但这种方法的问题在于列举的列名要写死，如果列名很多，case when 语句会很长，并不优雅。在2005版本就推出了pivot/unpivot关键字，可以方便的实现。这方面的资料已经很多，这里提供一个简单的示例，以便快速上手。

用到的关键函数：  

pivot() 快速实现行转列

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

unpivot() 快速实现列传行

UNPIVOT([转换后对应的列名(自定义起名)] for [转换后对应的列名(自定义起名)] in ([转换为行的列名1],[转换为行的列名2],[转换为行的列名3],........
[转换为行的列名N]))

一、SQL列转行

假设有一张表，结构和数据是这样子的：

想转换为如下的形式：

这个就是典型的列转行的形式，需要使用unpivot。

SQL实现：

select 姓名, 科目, val from [dbo].[科目成绩]
unpivot (val for 科目 in([语文],[数学],[英语])) as u

二、SQL行转列

现在假设已经有了这张表：

 

我们借用上面的SQL得到的临时表，将其还原为：

这是典型的行转列的实现，需要用到pivot。

SQL实现：

with aa as(
select 姓名,科目, val from [dbo].[科目成绩]
unpivot (val for 科目 in ([语文],[数学],[英语])) as u)

select * from aa pivot(max(val) for 科目 in ([语文],[数学],[英语])) as tt
order by [语文] desc,[数学] desc,[英语] desc;

得到如下表格：

按照语文、数学、英语的分数依次降序排列。

附加：

查询字段名和备注

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 = '列名';

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)

执行sql后得到如下表格：