stragg in 10gR2
well, you all know string aggregration
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
here is a suggestion with xquery in 10gR2
select deptno,
replace( replace( replace(
XMLQUERY('for $cc in ora:view("emp") let $ename:=$cc/ROW/ENAME/text() where $cc/ROW/DEPTNO/number()=$deptno/d/number() return <e>{$ename}</e>'
passing by value xmltype('<d>'||deptno||'</d>') as "deptno"
returning content
),'</e><e>', ','),'<e>'),'</e>') enames
from dept
/
DEPTNO ENAMES
------ ------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
40
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
here is a suggestion with xquery in 10gR2
select deptno,
replace( replace( replace(
XMLQUERY('for $cc in ora:view("emp") let $ename:=$cc/ROW/ENAME/text() where $cc/ROW/DEPTNO/number()=$deptno/d/number() return <e>{$ename}</e>'
passing by value xmltype('<d>'||deptno||'</d>') as "deptno"
returning content
),'</e><e>', ','),'<e>'),'</e>') enames
from dept
/
DEPTNO ENAMES
------ ------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
40
Posted by Laurent Schneider at 9/09/2005 10:58:00 AM
Permalink : stragg in 10gR2
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
5 Comments:
neato !
What do I need to do to make this work ? (XE/Linux)
Thanks
ORA-19114: error during parsing the XQuery expression:
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
http://forums.oracle.com/forums/thread.jspa?messageID=1229525�
XMLAgg and XMLElement
great anonymous comment ;-)
thanks a lot for this one!
select deptno,substr(replace(replace(XMLAgg(XMLElement("x",ename)),'</x>'),'<x>',','),2) enames from emp group by deptno
DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 JONES,ADAMS,FORD,SCOTT
30 ALLEN,JAMES,TURNER,WARD,MARTIN,BLAKE
40 SMITH
robert,
I guess there is no java in the oracle database express edition (and therefore no way to run the xquery classes)
greate article,
your XMLAgg solution in comment works on 9i and 10.1
thanks a lot.
Post a Comment
<< Home