[public] XMLAgg / ListAgg

gistfile1.txt

Oracle 9i XMLAgg String Aggregation: Concat Data from Rows to Col
=================================================================

Below is supported in 9i (may appear complicated at first look, but actually is rather simple). There are other advance features in this area for 10g & 11g

WITH table1 AS (
  SELECT 1 attr1 FROM DUAL UNION
  SELECT 2 attr1 FROM DUAL UNION
  SELECT 3 attr1 FROM DUAL UNION
  SELECT 4 attr1 FROM DUAL
)
SELECT * FROM TABLE1;

WITH table1 AS (
  SELECT 1 attr1 FROM DUAL UNION
  SELECT 2 attr1 FROM DUAL UNION
  SELECT 3 attr1 FROM DUAL UNION
  SELECT 4 attr1 FROM DUAL
)SELECT
  listagg(attr1, ', ' on overflow truncate) within group (order by 1) AS attr1, -- 11g only

  substr(regexp_replace(','||( 
    listagg(attr1, ',' on overflow truncate) within group (order by attr1) 
  ),'(,[^,]+)(\1)+','\1'),2) AS attr1, -- remove duplicates
  -- https://community.oracle.com/ideas/12533

  Trim(xmlagg(xmlelement("x", attr1))) AS XML,
  REPLACE(REPLACE(Trim(xmlagg(xmlelement("x", attr1))),'<x>',''),'</x>',', ') AS attr1,
  RTrim(xmlagg(xmlelement("x", attr1||', ')).extract('/*/text()'),', ') AS attr1
FROM TABLE1;

Notes:
 • xmlagg(xmlelement("x", attr1)) -> aggregate values -> form XML object
 • Trim -> converts XML object to string
 • replace -> remove the XML tags and replace with ‘,’
 • .extract(...) -> extract text data from XML (exclude all XML tagged)
 • RTrim -> converts XML object to string and trim the last portion of the string