Oracle中的列转行如何在PostgreSQL中实现

1. 前言

在Oracle中是通过unpivot来实现列转行的，在PostgreSQL中可以用json_each和json_build_object

2. Oracle中的unpivot的例子

造测试表和测试数据：

CREATE TABLE student2(st_name varchar2(30), "语文" number, "数学" number, "英语" number, "物理" number);
INSERT INTO student2 (st_name,"语文","数学","英语","物理")
VALUES('张三',87,90,82,78);
INSERT INTO student2 (st_name,"语文","数学","英语","物理")
VALUES('李四',77,85,65,65);

表的数据如下：

ORACLE中使用 unpivot就可以完成列转行：

SELECT * FROM student2 unpivot(score FOR course IN (
"语文" AS '语文',
"数学" AS '数学',
"英语" AS '英语',
"物理" AS '物理'));

上面SQL执行的结果为：

3. PostgreSQL中的写法

造测试表和测试数据：

create table student2(st_name text, "语文" int, "数学" int, "英语" int, "物理" int);
INSERT INTO student2 (st_name,语文,数学,英语,物理)
VALUES('张三',87,90,82,78),
     ('李四',77,85,65,65);

使用 json_each和json_build_object就可以完成列转行，SQL如下：

select st_name, (row).key as course, (row).value as score from (
  select st_name, json_each(
      json_build_object('语文', "语文", '数学',"数学", '英语', "英语", '物理', "物理")) as row from student2
  ) as st;

上面SQL执行的结果为：