如何使用SQL创建多个透视字段完成指定数据表结构转换

DevOps 小满

2026-9-30

提问内容

早上好，Stack Overflow社区的各位开发者：
我正尝试使用SQL创建多个透视字段，完成数据表结构转换，恳请各位提供可行的实现方案。

原始数据表结构及数据

id	r_1	r_2	r_3	q_1	q_2
1	38	9	4	17	31
2	8	27	38	35	16
3	37	9	22	14	11
4	40	21	40	29	45
5	14	33	2	41	42

期望转换后的数据结构

id	pv_1	pv_1 values	pv_2	pv_2 values
1	r_1	38
1	r_2	9
1	r_3	4
1			q_1	17
1			q_2	31
2	r_1	8
2	r_2	27
2	r_3	38
2			q_1	35
2			q_2	16
后续id对应数据逻辑同上

实现方案

思路

这个需求属于宽表的分类拆行操作，将r前缀字段作为第一组透视值、q前缀字段作为第二组透视值分别拆行后，用UNION ALL拼接结果即可完全匹配期望输出。

通用标准SQL代码（适配所有主流数据库）

假设原始表名为raw_data，代码如下：

-- 拆分r_前缀字段为pv_1组
SELECT 
  id,
  'r_1' AS pv_1,
  r_1 AS `pv_1 values`,
  NULL AS pv_2,
  NULL AS `pv_2 values`
FROM raw_data

UNION ALL
SELECT 
  id,
  'r_2' AS pv_1,
  r_2 AS `pv_1 values`,
  NULL AS pv_2,
  NULL AS `pv_2 values`
FROM raw_data

UNION ALL
SELECT 
  id,
  'r_3' AS pv_1,
  r_3 AS `pv_1 values`,
  NULL AS pv_2,
  NULL AS `pv_2 values`
FROM raw_data

UNION ALL
-- 拆分q_前缀字段为pv_2组
SELECT 
  id,
  NULL AS pv_1,
  NULL AS `pv_1 values`,
  'q_1' AS pv_2,
  q_1 AS `pv_2 values`
FROM raw_data

UNION ALL
SELECT 
  id,
  NULL AS pv_1,
  NULL AS `pv_1 values`,
  'q_2' AS pv_2,
  q_2 AS `pv_2 values`
FROM raw_data

-- 按id排序，匹配样例输出顺序
ORDER BY id, pv_1 IS NULL, pv_1, pv_2;