postgresql行列转换函数

postgresql行列转换函数

Pg提供相关行列转换函数string_agg和regexp_split_to_table。

1、行转列：string_agg

测试表
postgres=# select * from test.test_copy ;
eno | ename | job | mgr | hiredate | sal | comm | deptno 
------+--------+----------+------+---------------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1991-02-20 00:00:00 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1991-04-02 00:00:00 | 2975 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1991-09-28 00:00:00 | 1250 | 1400 | 30
7498 | JASON | ENGINEER | 7724 | 1990-02-20 00:00:00 | 1600 | 300 | 10
(4 rows)
将部门为30的员工的姓名合并起来
postgres=# Select deptno,string_agg(ename,',') from test.test_copy group by deptno;
deptno | string_agg 
--------+--------------
30 | ALLEN,MARTIN
10 | JASON
20 | JONES
(3 rows)

2、列转行：regexp_split_to_table

postgres=# select * from test.test