oracle和pg行列转换

sqlboy-yuzhenc

已于 2022-11-24 16:55:57 修改

阅读量3.1k

点赞数

于 2021-06-18 15:02:01 首次发布

本文链接：https://blog.csdn.net/qq_33445829/article/details/118026492

版权

greenplum 同时被 3 个专栏收录

36 篇文章 3 订阅

订阅专栏

postgresql

22 篇文章 3 订阅

订阅专栏

ORACLE

21 篇文章 2 订阅

订阅专栏

postgresql

--拆
with tmp_table as (
select
	'a' name1,
	'A,B,C,D'::varchar as t )
select
	name1,
	regexp_split_to_table(t, ',') t
from
	tmp_table;
	
--合
select 
	name1,
	string_agg(t,',' order by t) t 
from (
	select 'a' name1,'A' t
	union all 
	select 'a' name1,'B' t
	union all 
	select 'a' name1,'C' t
	union all 
	select 'a' name1,'D' t
) a
group by name1;

oracle

--拆
with tmp_table as (
    select
		 'a' name1
		,'A,B,C,D' as t 
	FROM dual
)
SELECT name1,regexp_substr(t,'[^,]+',1,LEVEL) t FROM tmp_table a
CONNECT BY LEVEL <= regexp_count(a.t,'\,\')+1;

--合
SELECT 
    name1,
    listagg(t,',')WITHIN GROUP(ORDER BY t) t 
FROM (
    select 'a' name1,'A' t FROM dual
    UNION ALL 
    select 'a' name1,'B' t FROM dual
    UNION ALL 
    select 'a' name1,'C' t FROM dual
    UNION ALL 
    SELECT 'a' name1,'D' t FROM dual
) a
GROUP BY name1;