如何在SQL中编写SELECT查询将表格数据转为实体键值对形式

该转换需求可通过原生SQL实现，核心逻辑是将横向存储的列字段，拆解为对应实体的「字段标识-字段值」配对行，最终输出标准键值对结构。

通用实现思路 #

• 先定位源表的唯一实体标识列（比如用户ID、订单ID这类能唯一标记一行实体的字段）
• 对所有需要转为键值对的业务列，分别生成独立的查询片段：提取实体ID、固定输出列名字符串作为键、对应列的实际值作为值
• 用UNION ALL把所有列对应的查询片段拼接，最后按实体ID、键名排序，让输出结构更规整

通用兼容写法示例 #

假设源表为biz_entity，包含字段：entity_id（实体唯一标识）、field_a、field_b、field_c，对应的键值对查询写法可兼容所有SQL引擎：

SELECT
    entity_id,
    'field_a' AS `key`,
    CAST(field_a AS CHAR) AS `value`
FROM biz_entity

UNION ALL

SELECT
    entity_id,
    'field_b' AS `key`,
    CAST(field_b AS CHAR) AS `value`
FROM biz_entity

UNION ALL

SELECT
    entity_id,
    'field_c' AS `key`,
    CAST(field_c AS CHAR) AS `value`
FROM biz_entity
ORDER BY entity_id, `key`;

这里强制做类型转换的原因是，数值、日期、布尔等非字符串类型直接参与UNION操作时，容易触发类型不匹配报错，统一转为字符串格式可以保证所有键值对的类型一致。

各数据库的简化实现方式 #

逐列写UNION ALL是兼容所有场景的保底写法，不同数据库还可以用内置函数减少重复代码，提升开发效率：

• PostgreSQL：使用jsonb_each_text将整行转为JSON结构后逐行拆键值，不需要手动枚举所有业务列
• MySQL 5.7及以上：结合内置JSON函数实现自动拆列，避免逐列写重复的SELECT片段
• Spark SQL、Hive等数仓引擎：使用stack函数一次性将多列转为指定的键值对行结构，拆分效率比逐列UNION更高

实现注意事项 #

• 拆分后的结果集规模为「原表行数 * 待拆分列数」，大表场景下需要提前评估数据量，避免产生超大结果集拉取耗时
• 如果不需要保留列值为空的键值对，可以提前在每个列的查询片段里加WHERE条件过滤空值，减少无效输出
• 如果后续有新增列的需求，通用UNION写法需要同步追加对应的拆分片段，基于JSON函数的方案不需要修改代码即可自动适配新列

内容的提问来源于stack exchange，提问作者Web Design