Categories
Blogroll sql

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

By Laurent Schneider

Oracle Certified Master

6 replies on “stragg in 10gR2”

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

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

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.

Leave a Reply

Your email address will not be published.