with t as (select 's1' mname, 'x1,x2' sname from dual union all select 's2' mname, 'x1,x2' sname from dual union all select 's3' mname, 'x1,x2' sname from dual union all select 's1' mname, 'x3,x4' sname from dual) select max(decode(mname, 's1', COL)) S1, max(decode(mname, 's2', COL)) S2, max(decode(mname, 's3', COL)) S3 from (select mname, ltrim(max(replace(sys_connect_by_path(sname, ',|'), '|', ',')), ',') col from (select t.*, row_number() over(partition by mname order by mname) rn from t) start with rn = 1 connect by prior rn = rn - 1 and prior mname = mname group by mname);
max(decode())或max(case when)或pivot,具体写法继续百度吧不是固定这几个部门,可以借助视图实现
百度 oracle 行转动态列 应该有相关实例
with t as
(select 's1' mname, 'x1,x2' sname
from dual
union all
select 's2' mname, 'x1,x2' sname
from dual
union all
select 's3' mname, 'x1,x2' sname
from dual
union all
select 's1' mname, 'x3,x4' sname
from dual)
select max(decode(mname, 's1', COL)) S1,
max(decode(mname, 's2', COL)) S2,
max(decode(mname, 's3', COL)) S3
from (select mname,
ltrim(max(replace(sys_connect_by_path(sname, ',|'), '|', ',')),
',') col
from (select t.*,
row_number() over(partition by mname order by mname) rn
from t)
start with rn = 1
connect by prior rn = rn - 1
and prior mname = mname
group by mname);