Oracle 列转行 行转列

列转行：

--方法一，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