greenplum数据库行列转换

在Greenplum数据库中，可以使用UNPIVOT和PIVOT函数来进行行列转换。

1. 行转列 - UNPIVOT函数
UNPIVOT函数可以将多列数据合并成一列，并且在每个值旁边添加一个新的列，来表示该值所在的原始列的名称。

例如，假设有以下数据表：

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product VARCHAR(50),
    january INT,
    february INT,
    march INT
);

INSERT INTO sales (product, january, february, march)
VALUES
    ('Product A', 100, 200, 300),
    ('Product B', 400, 500, 600);

可以使用UNPIVOT函数将每个月份的销售额转换为一列：

SELECT id, product, month, sales
FROM (
    SELECT id, product, january, february, march
    FROM sales
) AS s
UNPIVOT (
    sales FOR month IN (january, february, march)
) AS u;

输出结果如下：

id |  product   |  month   | sales 
----+------------+----------+-------
  1 | Product A  | january  |   100
  1 | Product A  | february |   200
  1 | Product A  | march    |   300
  2 | Product B  | january  |   400
  2 | Product B  | february |   500
  2 | Product B  | march    |   600

2. 列转行 - PIVOT函数
PIVOT函数可以将一列数据转换为多列，并且在每个新列的名称旁边添加一个新的列，来表示该值所在的原始列的名称。

例如，假设有以下数据表：

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product VARCHAR(50),
    month VARCHAR(20),
    sales INT
);

INSERT INTO sales (product, month, sales)
VALUES
    ('Product A', 'January', 100),
    ('Product A', 'February', 200),
    ('Product A', 'March', 300),
    ('Product B', 'January', 400),
    ('Product B', 'February', 500),
    ('Product B', 'March', 600);

可以使用PIVOT函数将每个月份的销售额转换为一列：

SELECT product,
       MAX(CASE WHEN month = 'January' THEN sales ELSE NULL END) AS january,
       MAX(CASE WHEN month = 'February' THEN sales ELSE NULL END) AS february,
       MAX(CASE WHEN month = 'March' THEN sales ELSE NULL END) AS march
FROM sales
GROUP BY product;

输出结果如下：

  product   | january | february | march 
------------+---------+----------+-------
 Product A  |     100 |      200 |   300
 Product B  |     400 |      500 |   600