SQL Server 行列互转实战：CASE WHEN 与 PIVOT/UNPIVOT 方法解析
在数据库查询中，行转列（Pivot）与列转行（Unpivot）是处理报表数据时的常见需求。本文将对比两种主流实现方式：传统且兼容性强的 CASE ... END 聚合函数法，以及 SQL Server 2005 起支持的专用关键字 PIVOT 和 UNPIVOT。通过具体的学生成绩表案例，展示如何构建测试数据、编写转换语句并验证最终结果。
场景准备：测试数据结构
为了直观展示转换效果，我们首先需要构建一个典型的“长表”结构。假设有一个存储学生科目成绩的临时表 @t，其中每一行代表一名学生在某一科目的得分。这种结构便于录入，但不利于横向对比每位学生的所有成绩。
declare @t table (StudentName nvarchar(20), Subject nvarchar(20), Score int) Insert into @t (StudentName,Subject,Score) values ( '学生A', '中文', 80 ); Insert into @t (StudentName,Subject,Score) values ( '学生A', '数学', 78 ); Insert into @t (StudentName,Subject,Score) values ( '学生A', '英语', 92 ); Insert into @t (StudentName,Subject,Score) values ( '学生B', '中文', 89 ); Insert into @t (StudentName,Subject,Score) values ( '学生B', '数学', 87 ); Insert into @t (StudentName,Subject,Score) values ( '学生B', '英语', 75 ); Insert into @t (StudentName,Subject,Score) values ( '学生C', '中文', 92 ); Insert into @t (StudentName,Subject,Score) values ( '学生C', '数学', 74 ); Insert into @t (StudentName,Subject,Score) values ( '学生C', '英语', 65 ); Insert into @t (StudentName,Subject,Score) values ( '学生D', '中文', 79 ); Insert into @t (StudentName,Subject,Score) values ( '学生D', '数学', 83 ); Insert into @t (StudentName,Subject,Score) values ( '学生D', '英语', 81 ); Insert into @t (StudentName,Subject,Score) values ( '学生E', '中文', 73 ); Insert into @t (StudentName,Subject,Score) values ( '学生E', '数学', 84 ); Insert into @t (StudentName,Subject,Score) values ( '学生E', '英语', 93 ); Insert into @t (StudentName,Subject,Score) values ( '学生F', '中文', 79 ); Insert into @t (StudentName,Subject,Score) values ( '学生F', '数学', 86 ); Insert into @t (StudentName,Subject,Score) values ( '学生F', '英语', 84 );
方法一：使用 CASE WHEN 聚合函数
这是最通用且兼容性最好的方法，适用于几乎所有支持 SQL 语法的数据库。其核心逻辑是利用 CASE 表达式判断当前行属于哪个科目，若匹配则返回分数，否则返回 0。最后通过聚合函数（如 SUM）按学生分组汇总。
第1步：编写行转列查询
- 目的：将不同科目的成绩分散在不同的列中显示。
- 关键参数：
GROUP BY StudentName确保每个学生只有一行输出。 - 操作：对每个科目编写独立的
sum(case when ... then ... else 0 end)字段。
select StudentName,
sum(case when Subject = N'中文' then Score else 0 end) Chinese,
sum(case when Subject = N'数学' then Score else 0 end) Math,
sum(case when Subject = N'英语' then Score else 0 end) English
from @t
group by StudentName
第2步：验证输出结果
执行后，表格将从原来的多行记录（每名学生多条记录）变为单行记录（每名学生一条记录包含各科分数）。此方法优点是逻辑直观，缺点是如果科目动态增加或数量巨大，SQL 语句会变得冗长。
方法二：使用 PIVOT 关键字（SQL Server 2005+）
PIVOT 是 SQL Server 提供的专用行转列运算符，语法更加紧凑，专门用于此类透视操作。它要求源数据必须先经过子查询处理。
第1步：理解 PIVOT 语法结构
PIVOT 的基本骨架如下：
pivot( aggregate_function(value_column) for pivot_column in ( ) ) AS alias
table_source：进行转换的数据源（通常是子查询）。aggregate_function：使用的聚合函数，通常为SUM、AVG等。value_column：需要被聚合的数值列（原表中的Score）。pivot_column：需要根据其唯一值来生成新列名的列（原表中的Subject）。in (...)：明确指定要生成的目标列名列表。
第2步：执行行转列操作
- 入口：直接对
@t表或其子查询应用PIVOT。 - 动作：指定对
Score求和，根据Subject的值生成[中文],[英语],[数学]列。
select StudentName,
[Chinese] = [中文],
[Math] = [数学],
[English] = [英语]
from
(select * from @t) t1
pivot (sum(Score) for Subject in([中文],[英语],[数学])) t2
注意：在 IN 子句中列出的顺序决定了结果集中新列的顺序。上述示例中特意调整了列名别名以符合英文习惯，实际使用中可直接引用生成的中文名列。
逆向操作：使用 UNPIVOT 列转行
如果之前通过 PIVOT 得到了宽表，可以使用 UNPIVOT 将其还原为原始的长表格式。这通常用于数据规范化或合并来自不同宽表的来源。
第1步：构建 UNPIVOT 查询
- 目的：将
[中文],[英语],[数学]这些列的名称和值重新提取回两列：Subject（列名）和Score（列值）。 - 关键区别：
UNPIVOT不需要聚合函数，因为它是在已经是“结果集”的基础上进行展开。
-- 先进行一次 Pivot 得到宽表，再立即 Unpivot 还原
select StudentName,
Subject,
Score
from
(select * from @t) t1
pivot (sum(Score) for Subject in([中文],[英语],[数学])) t2
unpivot (Score for Subject in([中文],[英语],[数学])) t3
第2步：比对原始数据
执行上述嵌套查询后，输出的数据应当与最初插入 @t 表的原始行数一致。这一步不仅验证了转换的准确性，也展示了如何利用这两个关键字进行数据的“变换-还原”循环测试。
总结与注意事项
- 性能考量：对于小规模数据，两者性能差异可忽略。但在大数据量下，
PIVOT可能涉及更多的内部索引查找和内存分配，建议在实际生产环境中针对具体数据集进行基准测试。 - 动态列处理：无论是
CASE还是PIVOT，如果科目名称是动态变化的（例如每个月份不同），都需要通过动态 SQL（Dynamic SQL）拼接语句来实现，静态 SQL 无法适应完全未知的列头。 - 空值处理：在
PIVOT中，如果某个学生在某科缺席导致无数据，该位置默认显示 NULL。如果需要默认值，可以在外层包裹ISNULL()或使用之前的ELSE 0技巧（但需注意PIVOT本身的机制是直接映射列名）。
本站声明
本文内容仅供参考，不代表本站观点。转载或引用请注明出处。如发现内容问题，请通过联系我们页面反馈。
