pgsql 列转行

pgsql 列转行

在 PostgreSQL 数据库中，我们经常会遇到将列转换为行的需求。常见的情况包括将某个表的多个列合并为一列，或者将一列数据拆分成多列。本文将详细介绍如何在 pgsql 中实现列转行的操作。

情景一：将多列转换为一列

假设我们有一个名为 students 的表，其中包含学生的学号、姓名和年龄。现在的要求是将学生的学号、姓名和年龄按照一定的格式合并为一列。

首先，我们需要创建一个示例表并插入一些数据，如下所示：

CREATE TABLE students (
  student_id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  age INTEGER
);

INSERT INTO students (name, age) VALUES ('张三', 18);
INSERT INTO students (name, age) VALUES ('李四', 20);
INSERT INTO students (name, age) VALUES ('王五', 22);

接下来，我们可以使用 CONCAT() 函数将多个列合并为一列：

SELECT CONCAT(student_id, ' - ', name, ' - ', age) AS student_info FROM students;

运行以上 SQL 语句，会返回一个包含学生信息的结果集，例如：

student_info
----------------------
1 - 张三 - 18
2 - 李四 - 20
3 - 王五 - 22

情景二：将一列数据拆分为多列

接下来，我们来看一个将一列数据拆分为多列的情景。假设我们有一个名为 books 的表，其中包含书籍的书名。现在要求将书名按照指定的长度进行拆分，并将拆分后的数据存储在多列中。

首先，我们需要创建一个示例表并插入一些数据，如下所示：

CREATE TABLE books (
  book_id SERIAL PRIMARY KEY,
  title VARCHAR(100)
);

INSERT INTO books (title) VALUES ('《百年孤独》');
INSERT INTO books (title) VALUES ('《1984》');
INSERT INTO books (title) VALUES ('《傲慢与偏见》');

接下来，我们可以使用 LEFT() 和 SUBSTRING() 函数将一列数据拆分为多列：

SELECT 
  LEFT(title, 3) AS part1,
  SUBSTRING(title FROM 4 FOR 3) AS part2,
  SUBSTRING(title FROM 7) AS part3 
FROM books;

运行以上 SQL 语句，会返回一个包含拆分后的数据的结果集，例如：

part1 | part2 |   part3
-------+-------+-----------
《百  |  年孤 | 独》
《198 |   4》  |
《傲慢 |   与偏 | 见》

情景三：将列名转换为行数据

有时候，我们需要将表中的列名作为行数据进行处理。这种情况通常发生在动态列名的场景中。下面是一个示例：

CREATE TABLE scores (
  id SERIAL PRIMARY KEY,
  student_id INTEGER,
  chinese INTEGER,
  math INTEGER,
  english INTEGER
);

INSERT INTO scores (student_id, chinese, math, english) VALUES (1, 80, 90, 85);
INSERT INTO scores (student_id, chinese, math, english) VALUES (2, 75, 85, 95);

为了将列名转换为行数据，我们可以使用 UNION 或者 UNION ALL 运算符。

SELECT 'chinese' AS subject, chinese AS score FROM scores
UNION ALL
SELECT 'math' AS subject, math AS score FROM scores
UNION ALL
SELECT 'english' AS subject, english AS score FROM scores;

运行以上 SQL 语句，会返回一个包含转换后的数据的结果集，例如：

subject  | score
----------+-------
chinese   |   80
math      |   90
english   |   85
chinese   |   75
math      |   85
english   |   95

通过以上操作，我们成功将表中的列名转换为了行数据。

总结一下，pgsql 提供了多种方法来实现列转行的操作，包括将多列转换为一列，将一列数据拆分成多列，以及将列名转换为行数据。开发人员可以根据实际需求选择适合的方法来完成相应的数据转换。