SQL中快速最优的行列转置实现方法 附宽表转长表示例

你要实现的列转行拆分操作，在SQL领域叫逆透视（UNPIVOT），也就是把宽表的多列独立值，拆解为“维度列+类型标识列+值列”的长表结构，有三类常用实现方案，适配不同数据库环境：

先说明：你给出的原表存在表头展示歧义，三个列名都标为Red属于排版问题，实际数据库不允许同表存在完全重名的字段，我们先给原表补全合规的字段命名作为演示基础：

-- 演示用原表结构（对应你给出的原始数据）
表名：color_record
字段说明：
- 人员名：Paul/John/Tim这类人员标识值
- 备注：xxx/yyy/zzz这类附加说明值
- 记录日期：12-01-2021这类业务日期值
- 红色标识值：对应原表第一个Red列下的数字值
- 绿色标识值：对应原表Green列下的数字值
- 蓝色标识值：对应原表Blue列下的数字值

全数据库通用写法 #

这种写法不需要依赖任何数据库的专属语法，在MySQL 5.x、PostgreSQL、SQL Server、Oracle等所有支持SQL标准的环境都能运行，灵活性最高：

-- 用UNION ALL拼接每个待转换列的查询结果
SELECT 
  人员名,
  备注,
  记录日期,
  'Red' AS Type,
  红色标识值 AS Value
FROM color_record

UNION ALL

SELECT 
  人员名,
  备注,
  记录日期,
  'Green' AS Type,
  绿色标识值 AS Value
FROM color_record

UNION ALL

SELECT 
  人员名,
  备注,
  记录日期,
  'Blue' AS Type,
  蓝色标识值 AS Value
FROM color_record

-- 按人员排序，匹配你期望的结果顺序
ORDER BY 人员名, Type;

这种写法的优势是不受类型限制：哪怕待转换的几个列数据类型不一样（比如有的存数字、有的存字符串），只要在查询时把Value列转成统一类型就能正常运行，不会出现兼容报错。

支持内置UNPIVOT语法的数据库写法 #

如果使用SQL Server、Oracle这类内置了UNPIVOT逆透视函数的数据库，可以省略重复的查询段，用更简洁的语法实现同等效果：

SELECT 人员名, 备注, 记录日期, Type, Value
FROM color_record
UNPIVOT (
  Value FOR Type IN (红色标识值, 绿色标识值, 蓝色标识值)
) AS unpivot_res
ORDER BY 人员名, Type;

注意：内置UNPIVOT要求所有参与转换的列数据类型完全一致，否则会触发类型不兼容报错，灵活度略低于UNION ALL写法。

MySQL 8.0+ 高性能写法 #

高版本MySQL可以用CROSS JOIN LATERAL搭配VALUES子句实现，比多段UNION ALL的执行效率更高，适合大表场景：

SELECT t.人员名, t.备注, t.记录日期, x.Type, x.Value
FROM color_record t
CROSS JOIN LATERAL (
  VALUES 
    ('Red', t.红色标识值),
    ('Green', t.绿色标识值),
    ('Blue', t.蓝色标识值)
) AS x(Type, Value)
ORDER BY t.人员名, x.Type;

实操注意点 #

• 你给出的原始表头存在排版重复问题（三个列均标注为Red），实际操作前需要先把重复列名修改为数据库允许的唯一字段名，否则会触发语法错误。
• 如果待转换的列数量较多，可通过数据库元数据动态生成对应转换SQL，避免手写大量重复代码提升维护成本。

内容的提问来源于stack exchange，提问作者BRAD BREAD