Postgres如何将单行多列数据转置为列名对应值的多行格式

你需要的操作是列转行（Unpivot），是Pivot（行转列）的反向操作，Postgres 中可以通过unnest函数快速实现：

适用场景：列数固定的情况 #

假设你的原表名为test_table，查询语句如下：

SELECT 
  unnest(ARRAY['a', 'b', 'c', 'd', 'e']) AS col_name,
  unnest(ARRAY[a, b, c, d, e]) AS col_value
FROM test_table;

更严谨的写法（Postgres 9.4及以上版本支持） #

多参数unnest可以保证多数组展开时的位置严格对应，避免错位问题：

SELECT u.col_name, u.col_value
FROM test_table,
unnest(
  ARRAY['a', 'b', 'c', 'd', 'e']::text[],
  ARRAY[a, b, c, d, e]::int[]
) AS u(col_name, col_value);

测试验证 #

你可以通过以下语句构造测试数据验证效果：

-- 构造测试表
CREATE TEMP TABLE test_table (a int, b int, c int, d int, e int);
INSERT INTO test_table VALUES (29, 14, 11, 16, 8);

-- 执行上述查询得到结果：
 col_name | col_value 
----------+-----------
 a        |        29
 b        |        14
 c        |        11
 d        |        16
 e        |         8

注意事项 #

如果你的列数据类型不一致，可以统一转为text类型再放入数组，例如将值数组修改为ARRAY[a::text, b::text, c::text, d::text, e::text]即可。

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