oracle sys_connect_by_path实现行列转换

//原帖：http://topic.youkuaiyun.com/u/20110412/16/3eec4221-d1f7-4cd2-95d0-27f3ff086632.html?77362 //前几天，一优快云友友问了一个行列转换的问题。 //问题之后的几分钟之内，好多优快云友友都给出了正确答案。 //但是，这位兄弟的一句话让大家很是郁闷： //"诺诺的答谢 楼上各位大大。。。只是纠结的说下：oracle版本没到10g。。。没有wm_concat" //wm_concat函数是10g才引入的。 //哎！真是一个杯具，提问时怎么不将oracle版本号贴出来呢？ //等了2天，终于，一位大虾来将此问题解决了，下面是代码： with tb as( select 'A' grade,'XX'name from dual union all select 'A','XY' from dual union all select 'A','YY' from dual union all select 'B','aa' from dual union all select 'B','bb' from dual) select grade,substr(max(sys_connect_by_path(name,';')),2) name from (select grade,name,row_number() over(partition by grade order by name) rn from tb) start with rn=1 connect by rn= prior rn+1 and connect_by_root(grade)=grade group by grade; -- GRADE NAME ----- -------------------------------------------------------------------------------- A XX;XY;YY B aa;bb -- 10g的实现： with t as( select 'A' grade,'XX'name from dual union all select 'A','XY' from dual union all select 'A','YY' from dual union all select 'B','aa' from dual union all select 'B','bb' from dual) select grade,wmsys.wm_concat(name) name from t group by grade; -- GRADE NAME ----- -------------------------------------------------------------------------------- A XX,XY,YY B aa,bb