1. 前言

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

2. Oracle中的unpivot的例子

造测试表和测试数据:

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

表的数据如下:

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

  1. SELECT * FROM student2 unpivot(score FOR course IN (
  2. "语文" AS '语文',
  3. "数学" AS '数学',
  4. "英语" AS '英语',
  5. "物理" AS '物理'));

上面SQL执行的结果为:

3. PostgreSQL中的写法

造测试表和测试数据:

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

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

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

上面SQL执行的结果为:

本站文章,未经作者同意,请勿转载,如需转载,请邮件customer@csudata.com.
0 评论  
添加一条新评论