SQL Server 行转列实战：静态 PIVOT、动态 SQL 与 CASE WHEN 详解

结果与目标

目标是将存储为多行记录的成绩数据（一用户对应多个科目的成绩），转换为标准表格形式，即每个用户仅占一行，不同的科目名称作为独立的列展示。这一操作能显著提升数据的可读性，便于直接进行横向比较和报表导出。

准备条件：相关数据表结构

在编写转换逻辑前，需了解基础表结构。主要涉及两张表：

1. Score表：存储原始成绩记录，通常包含用户ID、科目名称（ScoreName）和分数（Score）等字段。

2. [User]表：存储用户基本信息，包含用户ID（ID）和姓名（Name）。两表通过UID与ID进行关联。

真实操作顺序

以下提供三种常用的行转列实现方式，按实现难度和适用场景分类。

第1步：使用PIVOT函数（静态SQL）

目的：当需要转换的列（如英语、数学）数量固定且已知时，这是最简洁的语法。

入口：直接在查询语句中使用 PIVOT 子句。

动作：首先通过JOIN将Score表和User表合并，得到包含UID、Name、Score、ScoreName的结果集；然后使用PIVOT对分数进行聚合计算。

重要参数：AVG(Score) 指定聚合函数，FOR ScoreName IN ([英语], [数学]) 指定要转为列的源值及新列名。

--方法一：静态SQL
SELECT * FROM
(SELECT UID, Name, Score, ScoreName FROM Score,[User] WHERE Score.UID=[User].ID) AS SourceTable
PIVOT(AVG(Score) FOR ScoreName IN ([英语], [数学])) AS a

第2步：构建动态SQL（处理未知或变化的列）

目的：当科目列表不固定，或未来可能新增科目时，静态写法会导致遗漏。

入口：声明变量并动态拼接字符串。

动作：

1. 使用 @s NVARCHAR(4000) 收集所有不同的 ScoreName 并为它们加上方括号 QUOTENAME。
2. 通过 ISNULL 累加逗号分隔的列名字符串。
3. 将生成的列名字符串拼入主查询的 IN (...) 部分，并使用 EXEC 执行拼接后的SQL字符串。

可观察的完成状态：无论表中有多少种科目，SQL都能自动生成对应的列头并完成转换。

--方法二：动态SQL
DECLARE @s NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',', '') + QUOTENAME(ScoreName)
FROM (SELECT DISTINCT ScoreName FROM Score) as A ---确保列名唯一

Declare @sql NVARCHAR(4000)
SET @sql='
select r.* from
(select UID, Name, ScoreName, Score from Score,[User] where Score.UID=[User].ID) as t
pivot
(
max(t.Score)
for t.ScoreName in ('+@s+')
) as r'
EXEC(@sql)

第3步：使用 CASE WHEN 配合 GROUP BY（经典兼容写法）

目的：该方法兼容性最好，支持几乎所有关系型数据库，逻辑清晰但不易扩展大量列。

入口：标准的 SELECT ... GROUP BY 查询。

动作：利用 CASE WHEN 在分组过程中判断科目名称，匹配则取出分数，否则设为默认值（如0或NULL），再通过 MAX 聚合去重取单值。

重要参数：row_number() 用于生成序号，group by 需包含所有非聚合字段（UID, ID, Name）。

--方法三：Case When
SELECT
    row_number() OVER(ORDER BY [User].ID) as 编号,
    UID as 用户编号,
    Name as 姓名,
    MAX(CASE ScoreName WHEN '英语' THEN Score ELSE 0 END) as 英语,
    MAX(CASE ScoreName WHEN '数学' THEN Score ELSE 0 END) as 数学
FROM Score,[User]
WHERE Score.UID=[User].ID
GROUP BY UID,[User].ID,Name

简要总结

以上是对SQL Server实现行转列功能的详细内容，更多关于SQL Server进阶查询的资料请关注本站其它相关文章！