- 云服務(wù)器優(yōu)惠
- 阿里云優(yōu)惠券
- 騰訊云優(yōu)惠券
- 京東云優(yōu)惠券
- 站點(diǎn)信息
- 問(wèn)題反饋
- 郵箱：[email protected]
關(guān)注微信
?2016-2026 Matools All rights reserved,
粵ICP備17059708號(hào)
postgresql行列轉(zhuǎn)換函數(shù)
Pg提供相關(guān)行列轉(zhuǎn)換函數(shù)string_agg和regexp_split_to_table。
1、行轉(zhuǎn)列：string_agg
測(cè)試表 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)
將部門(mén)為30的員工的姓名合并起來(lái) 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、列轉(zhuǎn)行：regexp_split_to_table
postgres=# select * from test.test_str; no | name ----+------------ 1 |
a,b,c,d 2 | Jason Xian (2 rows) 將列信息轉(zhuǎn)換成行 postgres=# select
regexp_split_to_table(name,',') from test.test_str where no=1;
regexp_split_to_table ----------------------- a b c d (4 rows) postgres=#
select regexp_split_to_table(name,' ') from test.test_str where no=2;
regexp_split_to_table ----------------------- Jason Xian (2 rows) postgres=#
select regexp_split_to_table('hello world', '\s+'); regexp_split_to_table
----------------------- hello world (2 rows)