学步园

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