如何使用SQL中PIVOT/UNPIVOT实现宽表转长表的数据重构

你的需求为将宽表中table1到table28的多列值转为行存储，属于列转行场景，使用UNPIVOT算子即可实现，以下是不同数据库的可用写法：

支持UNPIVOT语法的数据库写法（适配SQL Server、Oracle、PostgreSQL 15+等） #

SELECT 
  lot,
  table_val AS `table`,
  table_no AS `table NO`,
  headno AS `head no`
  -- 其他需要保留的原始字段可在此处补充
FROM 你的原始表名
UNPIVOT (
  table_val FOR table_no IN (
    table1,table2,table3,table4,/* 补全table5到table27 */table28
  )
) AS unpivot_result;

MySQL 8.0+ 适配写法（MySQL原生不支持UNPIVOT语法，用横向连接实现） #

SELECT 
  t.lot,
  v.table_val AS `table`,
  v.table_no AS `table NO`,
  t.headno AS `head no`
  -- 其他需要保留的原始字段可在此处补充
FROM 你的原始表名 t
JOIN LATERAL (
  SELECT 'table1' AS table_no, table1 AS table_val UNION ALL
  SELECT 'table2' AS table_no, table2 AS table_val UNION ALL
  SELECT 'table3' AS table_no, table3 AS table_val UNION ALL
  SELECT 'table4' AS table_no, table4 AS table_val UNION ALL
  -- 依次补全table5到table28的对应逻辑
  SELECT 'table28' AS table_no, table28 AS table_val
) v;

注意事项 #

• table为SQL保留字，不同数据库的转义规则有差异，可根据实际使用的数据库替换为双引号/方括号转义
• 如果原始表中headno之外还有其他需要同步保留的字段，直接在SELECT子句中添加对应字段名即可
• 如果table列的数量不固定，可以结合动态SQL生成列名列表，避免手动拼写所有列名

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