pgsql行列转换_pgsql 行转列

目录

一、造测试数据

二、行转列

1.函数定义

2.语法

3.示例

三、列转行

1.函数定义

2.语法

3.示例

一、造测试数据

create table test (id int,json1 varchar,json2 varchar);insert into test values(1,\'111\',\'{111}\');insert into test values(2,\'111,222\',\'{111,222}\');insert into test values(3,\'111,222,333\',\'{111,222,333}\');select * from test;

造完数据如下 

二、行转列

1.函数定义

‌regexp_split_to_table是PostgreSQL中的一个函数，用于将一个字符串根据正则表达式进行分割，并将结果返回为一个表格，每个分割后的部分作为一行‌‌。

2.语法

regexp_split_to_table(string text, pattern text) → setof textstring：要分割的原始字符串。pattern：用于分割的正则表达式模式‌返回值，该函数返回一个setof text，即一个文本类型的行集合，包含所有分割后的部分

3.示例

select id ,json1 ,json2 ,regexp_replace(regexp_replace(json2,\'{\',\'\'),\'}\',\'\') no_json2 --剔除json2外面大括号 ,regexp_split_to_table(json1,\',\') as j1 --使用json1来转换结果 ,regexp_split_to_table(regexp_replace(regexp_replace(json2,\'{\',\'\'),\'}\',\'\'),\',\') as j2 --使用json2来转换结果from test ;

执行结果

三、列转行

1.函数定义

string_agg() 函数是 PostgreSQL 中的一个聚合函数，用于将一个列中的值连接成一个字符串。

2.语法

string_agg(column_name, separator)  column_name：要聚合的列名。separator：可选参数，用于连接各个值的分隔符。如果未指定或为空，则使用默认分隔符（逗号加空格）。

3.示例

--将j1用；拼接select string_agg (j1,\';\')from (select id ,json1 ,json2 ,regexp_replace(regexp_replace(json2,\'{\',\'\'),\'}\',\'\') no_json2 --剔除json2外面大括号 ,regexp_split_to_table(json1,\',\') as j1 --使用json1来转换结果 ,regexp_split_to_table(regexp_replace(regexp_replace(json2,\'{\',\'\'),\'}\',\'\'),\',\') as j2 --使用json2来转换结果from test ) t ;

执行结果

--根据id分组按j1用abc拼接select id,string_agg (j1,\'abc\')from (select id ,json1 ,json2 ,regexp_replace(regexp_replace(json2,\'{\',\'\'),\'}\',\'\') no_json2 --剔除json2外面大括号 ,regexp_split_to_table(json1,\',\') as j1 --使用json1来转换结果 ,regexp_split_to_table(regexp_replace(regexp_replace(json2,\'{\',\'\'),\'}\',\'\'),\',\') as j2 --使用json2来转换结果from test ) t group by id;

执行结果