在 PostgreSQL 我们往往会遇到这样的需求，需要将行数据转换为列数据进行展示，就是所谓的行转列。下面我举一个案例和具体的实现。
CREATE EXTENSION IF NOT EXISTS tablefunc;
CREATE TABLE ct (
id bigint,
category text,
cvalue numeric
);
INSERT INTO ct (id, category, cvalue)
VALUES
(1, 'A', 10),
(1, 'B', 20),
(2, 'A', 30),
(2, 'B', 40),
(3, 'A', 10),
(3, 'C', 20),
(4, 'A', 30),
(5, 'C', 40),
(6, 'D', 50);
cloud_test=# select * from ct;
id | category | cvalue
----+----------+--------
1 | A | 10
1 | B | 20
2 | A | 30
2 | B | 40
3 | A | 10
3 | C | 20
4 | A | 30
5 | C | 40
6 | D | 50
(9 rows)
cloud_test=#
--当 category 的取值是三个值时
SELECT *
FROM crosstab(
'SELECT id, category, cvalue
FROM ct
ORDER BY id, category',
'VALUES (''A''), (''B''),(''C'')'
) AS ct (id integer, "A" numeric, "B" numeric,"C" numeric);
cloud_test=# SELECT * FROM crosstab('SELECT id, category, cvalue FROM ct ORDER BY id, category','VALUES (''A''), (''B''),(''C'')') AS ct (id integer, "A" numeric, "B" numeric,"C" numeric);
id | A | B | C
----+----+----+----
1 | 10 | 20 |
2 | 30 | 40 |
3 | 10 | | 20
4 | 30 | |
5 | | | 40
6 | | |
(6 rows)
cloud_test=#
--当 category 的取值是四个值时
SELECT *
FROM crosstab (
'SELECT id, category, cvalue
FROM ct
ORDER BY id, category',
'VALUES (''A''), (''B''),(''C''),(''D'')'
) AS ct (id integer, "A" numeric, "B" numeric,"C" numeric,"D" numeric);
cloud_test=# SELECT * FROM crosstab ('SELECT id, category, cvalue FROM ct ORDER BY id, category','VALUES (''A''), (''B''),(''C''),(''D'')') AS ct (id integer, "A" numeric, "B" numeric,"C" numeric,"D" numeric);
id | A | B | C | D
----+----+----+----+----
1 | 10 | 20 | |
2 | 30 | 40 | |
3 | 10 | | 20 |
4 | 30 | | |
5 | | | 40 |
6 | | | | 50
(6 rows)
cloud_test=#
--备注
SELECT *
FROM crosstab(
'SELECT id, category, cvalue
FROM ct
ORDER BY id, category')
AS ct(id bigint, "A" numeric, "B" numeric,"C" numeric,"D" numeric);
