Postgresql 将行转置为列

将 PostgreSQL 表的行转置为列：简介与案例代码

sql
-- 安装 tablefunc 扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;
-- 使用 crosstab 函数进行行列转置
SELECT * FROM crosstab(
  'SELECT category, subcategory, value FROM transposed_data ORDER BY 1,2',
  'SELECT DISTINCT subcategory FROM transposed_data ORDER BY 1'
) AS final_result(category text, subcategory_1 text, subcategory_2 text, ...);

sql
CREATE TABLE sales_data (
  product_name text,
  january_sales int,
  february_sales int,
  march_sales int
);
INSERT INTO sales_data VALUES
('Product A', 100, 150, 200),
('Product B', 120, 180, 250),
('Product C', 80, 130, 170);

sql
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
  'SELECT product_name, month, sales FROM (
    SELECT product_name, unnest(ARRAY[1, 2, 3]) AS month, unnest(ARRAY[january_sales, february_sales, march_sales]) AS sales
    FROM sales_data
  ) AS data ORDER BY 1,2',
  'SELECT DISTINCT month FROM (SELECT unnest(ARRAY[1, 2, 3])) AS months ORDER BY 1'
) AS transposed_sales(product_name text, january int, february int, march int);