oracle 中行转列详细例子和多种方法

--场景1：A　　Ba　　1a　　2a　　3b　　1b　　2b　　3希望实现如下效果：a  1,2,3b  4,5create table tmp asselect 'a' A, 1 B from dual union allselect 'a' A, 2 B from dual union allselect 'a' A, 3 B from dual union allselect 'b' A, 4 B from dual union allselect 'b' A, 5 B from dual;1.方法1：listagg--listagg() + group by: 推荐使用select a,listagg(b,',')  within group (order by b) as c from tmp group by a;--listagg() + over(partition by )select distinct a,listagg(b,',')  within group (order by b) over(partition by a) as c from tmp ;2.wm_concatselect a,to_char(wm_concat(b)) as b from tmp group by a3.sys_connect_by_pathselect a, max(substr(sys_connect_by_path(b, ','), 2)) str  from (select a, b, row_number() over(partition by a order by b) rn from tmp) start with rn = 1connect by rn = prior rn + 1    and a = prior a  group by a; 4.max+decodeselect a,       max(decode(rn, 1, b, null)) ||       max(decode(rn, 2, ',' || b, null)) ||       max(decode(rn, 3, ',' || b, null)) str  from (select a,b,row_number() over(partition by a order by b) as rn from tmp)  group by a order by 1;5.row_number()+leadselect a, str b  from (select a,               row_number() over(partition by a order by b) as rn,               b || lead(',' || b, 1) over(partition by a order by b) ||               lead(',' || b, 2) over(partition by a order by b) ||                lead(',' || b, 3) over(partition by a order by b) as str          from tmp) where rn = 1 order by 1;6.model语句select a, substr(str,2) b  from tmp   model return updated rows partition by(a) dimension by(row_number()  over(partition by a order by b) as rn)   measures(cast(b as varchar2(20)) as str)   rules upsert iterate(3) until(presentv(str[iteration_number+2],1,0) = 0)  (str [0] = str [0]||','||str[iteration_number + 1]) order by 1;