列转行:
--方法一,listagg
with table_name as (
select 1 as a, 1 as b from dual
union all
select 1 as a, 2 as b from dual
union all
select 3 as a, 1 as b from dual
)
select t.a, listagg(t.b, ',')
within group (order by t.a)
from table_name t
GROUP BY t.a
;
--方法二:wm_concat
with table_name as (
select 1 as a, 1 as b from dual
union all
select 1 as a, 2 as b from dual
union all
select 3 as a, 1 as b from dual
)
select t.a, wm_concat(t.b)
from table_name t
group by t.a
;
--方法三:connect by
with table_name as (
select 1 as a, 1 as b from dual
union all
select 1 as a, 2 as b from dual
union all
select 3 as a, 1 as b from dual
)
select f.a
,ltrim(max(sys_connect_by_path(f.b, ','))
keep (dense_rank last order by f.pnum), ',') as b
from
(
select t.a
, t.b
, row_number() over(partition by t.a order by t.a) as pnum
, row_number() over(partition by t.a order by t.a)-1 as lnum
from
(
select t1.a, t1.b
from table_name t1
) t
) f
group by f.a
connect by f.lnum = prior f.pnum and f.a = prior f.a
start with f.pnum = 1;
行转列
with temp as ( select level l_count from dual connect by level<=1000 )
select t.usertype,regexp_substr(t.productid,'[^,]+',1,t1.l_count) as productid
from tbl_user_type t,temp t1
where t1.l_count <=length(t.productid) - length(replace(t.productid,','))+1