oracle sys_connect_by_path实现行列转换

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