postgresql行列转换
postgresql行转列--行转列with tmp_table as (select'a' name1,'A,B,C,D'::varchar as t )selectname1,regexp_split_to_table(t, ',') tfromtmp_table;postgresql 行转列
·
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;
更多推荐
已为社区贡献1条内容
所有评论(0)