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 '物理'));
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执行的结果为：
8本站文章，未经作者同意，请勿转载，如需转载，请邮件customer@csudata.com.
