SQL Server 行列互转实战：Pivot/Unpivot 与 CASE WHEN 应用指南

行转列：将科目分数从多行合并为一行

假设有一张学生成绩表 Class，原始数据结构为学生姓名、课程名称、分数三个字段。目标是将不同的课程名转换为列头，分数作为对应的值。

第1步：构建测试数据环境

首先创建包含学生、课程和分数的基础表，并插入演示数据：

-- 1、行互转
-- 生成測試數據
if not object_id('Class') is null drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all select N'张三',N'数学',87 union all select N'张三',N'英语',82 union all select N'张三',N'物理',90
union all select N'李四',N'语文',65 union all select N'李四',N'数学',77 union all select N'李四',N'英语',65 union all select N'李四',N'物理',85
Go

第2步：使用 CASE WHEN 实现静态行转列 (兼容 SQL 2000)

这是最通用的方法，通过条件判断配合聚合函数将行数据转为列。如果课程固定且数量少，可直接编写静态 SQL。

select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from Class group by [Student]

第3步：使用动态 SQL 实现通用行转列 (适配未知课程)

当课程列表不固定时，需查询数据库获取所有课程名，拼接成 SQL 语句执行。以下分别展示 SQL 2000 和 2005+ 两种方式的动态逻辑。

SQL 2000 动态方案：利用字符串拼接生成 CASE WHEN 语句。

declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')

SQL 2005+ 动态方案：利用 PIVOT 关键字，语法更简洁。

declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

第4步：增加汇总统计（如总成绩）

在转换过程中，经常需要保留或增加一行汇总数据。可以通过在 SELECT 中直接添加聚合列来实现。

静态写法（含总成绩）：

select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) -- 加多一列(学科平均分用avg([Score]))
from Class group by [Student]

SQL 2005 动态写法（含总成绩）：使用窗口函数 SUM() OVER() 提前计算总和。

declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')

列转行：将各科分数列还原为记录行

当数据以宽表形式存储（每一门课程是一个列）时，若需要将每门课的分数展开为独立的行记录，需要进行列转行操作。

第1步：构建宽表测试数据

注意这里排除学生姓名列，只针对具体的分数列进行转换。

-- 2、列转行
-- 生成測試數據
if not object_id('Class') is null drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all select N'张三',87,90,82,78
Go

第2步：使用 UNION ALL 实现静态列转行

逐个列进行 Union 操作，是最基础的方法。

select * from (
select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class
)t order by [Student],[Course]

第3步：使用动态 SQL 处理多列 (SQL 2000)

通过查询系统视图 syscolumns 获取除主键外的其他列名，动态拼接 UNION ALL 语句。

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--[Column名称作为新列值]+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

第4步：使用 UNPIVOT 实现简化列转行 (SQL 2005+)

PIVOT 的反向操作是 UNPIVOT，能显著减少代码量。同样支持动态生成列名单。

静态 UNPIVOT：

select Student,[Course],[Score]
from Class
unpivot ([Score] for [Course] in([数学],[物理],[英语],[语文]))b

动态 UNPIVOT：

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

上述动态脚本生成的最终效果如下：

*-*-
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78

(8 行受影响)
*

总结

在实际开发中，如果是 SQL Server 2005 及以上版本，强烈建议优先使用内置的 PIVOT 和 UNPIVOT 操作符，因为它们可读性更强且性能经过优化。对于需要适应动态列名的复杂报表场景，结合 syscolumns 或新版的 INFORMATION_SCHEMA.COLUMNS 视图编写动态 SQL 是必要的技巧。以上就是 SQL Server 行列互转实现的详细内容，更多关于 SQL Server 数据透视与翻转的资料请关注本站其它相关文章！