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

6 thoughts on “stragg in 10gR2”

  1. Robert

    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

  3. Laurent Schneider

    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

  5. SnippetyJoe

    Hi Laurent,

    You might be interested in a string aggregation technique I came up with recently that uses a reference model. You can find it at http://www.sqlsnippets.com/en/topic-11754.html .

    I’ve also got a few pages that describe other popular techniques (along with performance metrics to compare them) starting at http://www.sqlsnippets.com/en/topic-11787.html . I wanted to include your XQuery approach (looks like a sharp one BTW) on my site, but unfortunately I only have XE so I couldn’t test it. I included a link to your blog post about it though at the end of my string aggregation section.

Comments are closed.