PostgreSQL中如何无硬编码转置部分表并将列名存为新列值？

一、固定列场景：高效硬编码方案 #

如果需要转置的列是固定的，用LATERAL VALUES是性能最优的方式，完全适配大数据量场景：

SELECT 
  t.StoreHouse, 
  t.Product, 
  v.Parameter, 
  v.Status
FROM your_table t
LATERAL (
  VALUES 
    ('HasDiscount', t.HasDiscount),
    ('IsOutOfStock', t.IsOutOfStock)
) v(Parameter, Status);

该方法通过集合操作直接生成目标行，避免逐行遍历的性能损耗，执行效率和原表扫描相当。

也可以用UNNEST结合数组实现，写法更紧凑：

SELECT 
  StoreHouse, 
  Product, 
  unnest(array['HasDiscount', 'IsOutOfStock']) AS Parameter,
  unnest(array[HasDiscount, IsOutOfStock]) AS Status
FROM your_table;

注意：如果转置列类型不一致，需统一类型（比如转成text），避免类型不匹配错误。

二、无硬编码的动态方案 #

如果需要转置的列可能变化，无需硬编码列名，可以通过PostgreSQL系统表information_schema.columns动态生成SQL：

方式1：直接执行动态查询 #

DO $$
DECLARE
  target_cols text;
BEGIN
  -- 拼接需要转置的列的VALUES子句
  SELECT string_agg(format('(''%s'', t.%s)', column_name, column_name), ', ')
  INTO target_cols
  FROM information_schema.columns
  WHERE table_name = 'your_table'  -- 替换为你的表名
    AND column_name NOT IN ('StoreHouse', 'Product');  -- 排除不需要转置的列

  -- 执行动态生成的查询
  EXECUTE format('
    SELECT 
      t.StoreHouse, 
      t.Product, 
      v.Parameter, 
      v.Status
    FROM your_table t
    LATERAL (VALUES %s) v(Parameter, Status)
  ', target_cols);
END $$;

方式2：创建可复用的函数 #

如果需要多次调用，可封装成函数：

CREATE OR REPLACE FUNCTION transpose_partial_columns(table_name text, exclude_cols text[])
RETURNS TABLE(StoreHouse text, Product text, Parameter text, Status boolean) AS $$
DECLARE
  target_cols text;
BEGIN
  SELECT string_agg(format('(''%s'', t.%I)', column_name, column_name), ', ')
  INTO target_cols
  FROM information_schema.columns
  WHERE table_name = $1
    AND column_name <> ALL($2);

  RETURN QUERY EXECUTE format('
    SELECT 
      t.StoreHouse, 
      t.Product, 
      v.Parameter, 
      v.Status
    FROM %I t
    LATERAL (VALUES %s) v(Parameter, Status)
  ', $1, target_cols);
END $$ LANGUAGE plpgsql;

调用示例：

SELECT * FROM transpose_partial_columns('your_table', ARRAY['StoreHouse', 'Product']);

关键说明 #

• crosstab工具适用于行转列（透视），而你需要的是列转行（逆透视），因此不适用。
• 动态方案的性能开销仅在SQL生成阶段，执行阶段和硬编码方案效率一致，远优于逐行遍历生成记录的方式，可应对大数据量场景。
• 如果转置列类型不一致，可将Status字段统一为text类型，避免类型冲突。

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