PostgreSQL 实现将多行合并转为列

下面是详细讲解"PostgreSQL 实现将多行合并转为列"的完整攻略。

背景

假设当前有如下一张表格table1，其中id列为主键，col_name列为需要转为列的字段名称，col_value列为需要转为列字段对应的值。

id	col_name	col_value
1	name	John
1	age	30
1	gender	Male
2	name	Emily
2	age	25
2	gender	Female
3	name	Tom
3	age	40
3	gender	Male

现在需要将这张表的每个id合并为一行数据，如下表。

id	name	age	gender
1	John	30	Male
2	Emily	25	Female
3	Tom	40	Male

解决方案

PostgreSQL有一个非常强大的函数crosstab()，可以非常方便地进行行转列的操作。它需要两个参数——一个为查询结果集，另一个为两个参数值之间的分隔符。接下来是具体的操作步骤。

创建临时表

首先，需要在当前数据库中创建一个临时表，命名为table2，以存储将要合并后的结果数据。

CREATE TEMPORARY TABLE table2 (
  id int,
  name varchar,
  age int,
  gender varchar
);

使用crosstab进行行转列

使用下面的语句进行crosstab()函数的调用：

SELECT *
FROM (SELECT id, col_name, col_value FROM table1) AS a
CROSSTAB (
  col_name, col_value
) AS result (
  name VARCHAR, age INT, gender VARCHAR
);

该语句将以id为主键，按照col_name列的值进行行转列操作，即将name、age和gender分别作为新表的列名，将对应的col_value放入相应的列中，生成的结果表格与目标表相似。注意，使用CROSSTAB()函数的前提是需要在服务器上安装tablefunc扩展包，它是PostgreSQL的一个标准扩展包，可以用以下命令进行安装：

CREATE EXTENSION IF NOT EXISTS tablefunc;

将结果插入到临时表中

INSERT INTO table2(id, name, age, gender)
SELECT *
FROM (SELECT id, col_name, col_value FROM table1) AS a
CROSSTAB (
  col_name, col_value
) AS result (
  name VARCHAR, age INT, gender VARCHAR
);

将第二步中生成的结果表格插入到临时表table2中，以便后续的处理操作。

查看结果

最后，使用下面的查询语句查看临时表table2中的结果。

SELECT * FROM table2;

其中的查询结果将是如下的目标表。

id	name	age	gender
1	John	30	Male
2	Emily	25	Female
3	Tom	40	Male

示例说明

示例1

假设table1表中仅包含了name和gender两个字段，而age字段为空，那么生成的表格如下所示：

id	col_name	col_value
1	name	John
1	gender	Male
2	name	Emily
2	gender	Female

使用上述的操作过程，可生成以下的结果表格：

id	name	age	gender
1	John		Male
2	Emily		Female

示例2

假设table1表中有大量字段，而我们只需要将name、age和gender这三个字段进行行转列操作，那么需要修改第二步中的语句，将要转换的字段列名列在括号内。例如：

SELECT *
FROM (SELECT id, col_name, col_value FROM table1 WHERE col_name IN ('name', 'age', 'gender')) AS a
CROSSTAB (
  col_name, col_value
) AS result (
  name VARCHAR, age INT, gender VARCHAR
);

修改后的结果如下：

id	name	age	gender
1	John	30	Male
2	Emily	25	Female
3	Tom	40	Male

以上就是“PostgreSQL 实现将多行合并转为列”的完整攻略。