sql server “动态pivot行转列”理解

1.什么是行转列？

最近工作中要用到动态行转列的知识，下面描述一下情景，何为“行转列”：

在表score中，有有关学生的一些信息，这些信息包括学生姓名，学科名称和分数

表score

name subject  score

张三     语文       78

李四     数学       83

王五     数学       59

张三     数学       99

李四     英语       90

李四     语文       77

王五     英语       86

现在我们需要对表score进行处理，得出如下的形式的表：

name“语文”“数学”“英语”

张三      78           59         0

李四      77           83         90

王五      0             59         86

原本“语文”“数学”“英语”这些字段是出现在行中的，既是行的一些记录中的，现在经过变换过程，成了表的列属性，这种过程就称作“行转列”。sql server的存储过程支持行转列，即PIVOT，那么对应的列转行就叫做UNPIVOT.

2.行转列（PIVOT）实现方式

我想先把上面的过程写出来，然后再分析语法的关键处。

1).最简单的方法：静态拼接行转列

select name,

sum(case [subject] when ‘语文’ then [score] else 0 end) as ‘语文’ ,

sum(case [subject] when ‘数学’ then [score] else 0 end) as ‘数学’ ,

sum(case [subject] when ‘英语’ then [score] else 0 end) as ‘英语’

from [dbo].[score]

2).静态PIVOT行转列

select *

from ( select [name],[subject],[score] from [dbo].[score]) p pivot

(sum([score) for [subject] in ([语文],[数学],[英语])) as pvt order by pvt.[name]

3).动态PIVOT行转列：

DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @tableName SYSNAME --行转列表
DECLARE @groupColumn SYSNAME --分组字段
DECLARE @row2column SYSNAME --行变列的字段
DECLARE @row2columnValue SYSNAME --行变列值的字段
SET @tableName = 'TestRows2Columns'
SET @groupColumn = 'UserName'
SET @row2column = 'Subject'
SET @row2columnValue = 'Source'

--从行数据中获取可能存在的列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])
    FROM ['+@tableName+'] GROUP BY ['+@row2column+']'
--PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col

SET @sql_str = N'
SELECT * FROM (
    SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT
    (SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.['+@groupColumn+']'
--PRINT (@sql_str)
EXEC (@sql_str)

这个理解起来有点难度，也是参照一个大大写的才慢慢看明白的，附上他的博客连接：http://www.cnblogs.com/gaizai/p/3753296.html

3.行转列（PIVOT）语法

pivot的语法分三步走：

1).先把要pivot的原始资料查询(Query)好，像上面的那个例子，表score就是第一个步骤查询好的资料；

2).设定好pivot的栏位与方式，上例中，sum([score) for [subject] in ([语文],[数学],[英语])就是第二步骤；

3).依照pivot好了的资料，呈现结果。也就是主句select 这种形式的。