动态行转列的核心思路
传统的行转列写法需要提前明确要转换的列的所有可能值，然后通过固定的CASE语句逐个生成列，当列值不固定或者会频繁新增时，这种写法维护成本很高。动态SQL拼接CASE语句的核心逻辑是先查询出所有需要转换为列的字段值，再将这些值拼接成CASE语句的字符串，最后执行拼接完成的SQL语句，实现动态生成列的效果。
实现步骤拆解
- 第一步：查询出需要作为列的所有唯一值，存储到临时变量或者临时表中
- 第二步：遍历这些唯一值，拼接CASE语句的字符串，每个值对应一个转换后的列
- 第三步：将拼接好的CASE部分与基础查询语句组合成完整的动态SQL
- 第四步：执行动态SQL，得到行转列后的结果
MySQL中的实现示例
假设我们有一张成绩表score，结构如下：
| student_name | subject | score |
|---|---|---|
| 张三 | 语文 | 90 |
| 张三 | 数学 | 85 |
| 李四 | 语文 | 88 |
| 李四 | 数学 | 92 |
| 李四 | 英语 | 80 |
我们需要把subject列的不同值转换为列，展示每个学生的各科成绩，科目会动态新增，不能写死。
动态SQL实现代码
首先拼接CASE语句部分：
-- 定义变量存储拼接的CASE语句
SET @sql = '';
-- 查询所有科目，拼接CASE语句
SELECT GROUP_CONCAT(DISTINCT CONCAT(
'MAX(CASE WHEN subject = ''',
subject,
''' THEN score END) AS `',
subject,
'`'
)) INTO @sql
FROM score;
然后拼接完整的查询SQL并执行：
-- 拼接完整SQL
SET @final_sql = CONCAT(
'SELECT student_name, ',
@sql,
' FROM score GROUP BY student_name'
);
-- 执行动态SQL
PREPARE stmt FROM @final_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
执行后得到的动态行转列结果如下：
| student_name | 语文 | 数学 | 英语 |
|---|---|---|---|
| 张三 | 90 | 85 | NULL |
| 李四 | 88 | 92 | 80 |
SQL Server中的实现示例
SQL Server中可以使用游标遍历科目值来拼接CASE语句，同样基于上面的score表结构：
-- 定义变量
DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @subject NVARCHAR(50)
-- 游标遍历所有科目
DECLARE subject_cursor CURSOR FOR
SELECT DISTINCT subject FROM score
OPEN subject_cursor
FETCH NEXT FROM subject_cursor INTO @subject
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', MAX(CASE WHEN subject = ''' + @subject + ''' THEN score END) AS [' + @subject + ']'
FETCH NEXT FROM subject_cursor INTO @subject
END
CLOSE subject_cursor
DEALLOCATE subject_cursor
-- 拼接完整SQL并执行
SET @sql = 'SELECT student_name' + @sql + ' FROM score GROUP BY student_name'
EXEC sp_executesql @sql
注意事项
- 拼接字符串时要注意单引号的转义，避免语法错误，不同数据库的字符串拼接语法有差异，需要根据实际使用的数据库调整
- 如果转换的列值包含特殊字符，需要做好字符转义处理，避免生成的SQL出现语法问题
- 当数据量较大时，建议先给查询唯一值的字段加索引，提升拼接SQL的效率
- 动态SQL执行前可以先打印拼接后的SQL语句，检查语法是否正确，方便排查问题