多行变一行

准备

表结构

CREATE TABLE tmp_book(
book_id int8 NOT NULL,
book_name varchar(48)  NOT NULL
);

数据

SELECT * FROM tmp_book;

image.png

函数

string_agg(字段名，分隔符)

SELECT book_id,string_agg(book_name,',') FROM tmp_book GROUP BY book_id;

image.png

array_agg(字段名)

select book_id,array_agg(book_name) from tmp_book group by book_id;

image.png

一行变多行

准备

表结构

CREATE TABLE tmp_book(
book_id int8 NOT NULL,
book_name varchar(48)  NOT NULL
);

数据

SELECT * FROM tmp_book;

image.png

函数

unnest(数组)

SELECT book_id,string_to_array(book_name,','),unnest(string_to_array(book_name,',')) FROM tmp_book;

image.png

regexp_split_to_table(字符串，分隔符)

select book_id,regexp_split_to_table(book_name,',') from tmp_book;

image.png

字符串变数组

string_to_array

SELECT book_id,book_name,string_to_array(book_name,',') FROM tmp_book;

image.png

regexp_split_to_array

select book_id,book_name,regexp_split_to_array(book_name,',') from tmp_book;

image.png