SQL Server 动态行转列：Group By 结合 Case With Pivot 及 SQL 生成法

静态行转列：Group By 与 Case 的结合

当需要统计的维度（如考试科目）是已知的固定集合时，可以使用 GROUP BY 配合多个 CASE WHEN 表达式进行行转列。这种方法逻辑清晰，但缺点是每个新增科目都需要手动修改 SQL 代码。

第1步：构建基础透视查询

目的：将学生姓名作为行，各科成绩作为列输出。
关键逻辑：使用 SUM(CASE ... END) 对特定科目进行条件聚合，非匹配项返回 NULL，外层包裹 ISNULL(..., 0) 确保显示为 0。

select stdname,
    isnull(sum( case stdsubject when '化学' then Result end), 0 ) as [化学],
    isnull(sum( case stdsubject when '数学' then Result end), 0 ) as [数学],
    isnull(sum( case stdsubject when '物理' then Result end), 0 ) as [物理],
    isnull(sum( case stdsubject when '语文' then Result end), 0 ) as [语文]
from #student
group by stdname

结果说明：上述查询会将原本分散在不同行的同一学生的不同科目记录，合并为一行，每个科目成为一个独立的字段列。如果某学生缺考某科，该列将显示为 0。

动态行转列：SQL 语句的动态生成

当需要转换的列名（如科目列表）不确定或经常变化时，硬编码 CASE 变得不再适用。此时可以通过动态拼接 SQL 字符串的方式，先查询出所有唯一的列名，再构造完整的执行语句。

第2步：动态拼接列头

入口：使用子查询获取去重后的列值列表。
动作：遍历唯一值列表，拼接到主查询字符串中。
参数：变量 @sql 用于存储生成的 SQL 语句。

declare @sql varchar(4000)
set @sql = ' select stdname '
select @sql = @sql + ',isnull(sum(case stdsubject when '''+ stdsubject +''' then Result end),0) ['+ stdsubject +']'
from (select distinct stdsubject from #student) as a
select @sql = @sql + ' from #student group by stdname '
print @sql
exec(@sql)

原理：内部子查询 (select distinct stdsubject from #student) 提取所有出现的科目名称。外部循环将这些名称追加到 SELECT 语句中，最终生成一个包含所有动态列名的标准 SQL 并执行。

高级技巧：基于系统表的通用自动化方案

在某些极端场景下，甚至希望根据数据库结构自动反转格式（例如从宽表转为长表，或反之）。以下示例展示了如何查询临时表的系统目录视图，自动生成处理脚本。

第3步：读取系统元数据并生成 UNION ALL

目的：获取表结构中除主键外的所有列名。
入口：查询 tempdb.dbo.syscolumns 系统表。
步骤：

1. 提取指定表的列名存入临时表 #tmpCloumns。
2. 遍历列名，生成 UNION ALL 连接的长格式 SQL。
3. 去除首部的多余连接符并排序执行。

-- 1. 提取列名
select [name] into #tmpCloumns
from tempdb.dbo.syscolumns
where id = object_id('tempdb.dbo.#student2')
and [name] <> 'stdname'

-- 2. 生成动态 UNION SQL
declare @strSql nvarchar(800)
select @strSql = ''
select @strSql = @strSql + ' union all '+ char(10)+ char(13) + 
'select [stdname], '''+[name]+''' as [科目],[ '+[name]+'] '+char(10)+char(13)+ 
'from [#student2] '+char(10)+char(13)
from #tmpCloumns

-- 3. 清理尾部空格并执行
select @strSql = substring(@strSql, 11,len(@strSql)) + ' order by stdname,[科目]'
exec(@strsql)

注意：此方法依赖于对系统表结构的理解，且需注意 SQL 注入风险（尽管在受控的内部脚本环境中较安全）。在现代 SQL Server 版本中，更推荐使用内置的 PIVOT 操作符或 CONVERT 函数来实现类似功能，以提高性能和维护性。

总结

SQL 的行转列操作灵活多变。对于已知列，简单的 Group By + Case 最为直观稳定；对于未知列，动态 SQL 拼接提供了必要的弹性。了解底层机制有助于在面对复杂的数据透视需求时选择最合适的方案。以上就是SQL动态行转列的详细技术解析，更多关于SQL Server数据处理技巧的资料请关注本站其它相关文章！