• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: WM_CONCAT

5 ways to aggregate columns into a comma separated string

05 Monday Oct 2009

Posted by troroblog in ORACLE 11g Release 2, Uncategorized

≈ Leave a comment

Tags

CONNECT BY, LISTAGG, ORACLE, SQL, WM_CONCAT, XMLAGG

Connect By

The first example uses a recursive query which starts with the first node of every group and only shows those rows, where the whole string has been aggregated (connect_by_isleaf = 1).

SELECT deptno
     , rn
     , TRIM(leading ',' FROM SYS_CONNECT_BY_PATH(ename,',')) 
  FROM (SELECT deptno
             , ename
             , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn
          FROM emp)
  WHERE connect_by_isleaf = 1
  START WITH rn = 1
 CONNECT BY PRIOR deptno = deptno
       AND PRIOR rn + 1 = rn
 /

XMLAGG

another solution is to use xml functions like xmlagg (aggregate) and xmlelement.

SELECT deptno
      ,RTRIM(XMLAGG(XMLELEMENT(e,ename,',').EXTRACT('//text()')),',') name
  FROM emp
 GROUP BY deptno
 /

 WM_CONCAT

one more solution even if not documented…

SELECT deptno, WM_CONCAT(ename)
  FROM emp
 GROUP by deptno
 /

and if you need the enames to be ordered…

SELECT deptno
     , WM_CONCAT(ename)
 FROM (SELECT deptno, ename 
         FROM emp 
        ORDER BY ename)
 GROUP by deptno
 /

 User defined aggregator

Another solution is to write your own group function

CREATE OR REPLACE TYPE ListAggregator IS OBJECT (
   vList VARCHAR2(4000)
  ,STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT ListAggregator) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateIterate (SELF  IN OUT ListAggregator
                                       , value IN     VARCHAR2 ) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateTerminate (self        IN OUT ListAggregator
                                         , returnValue    OUT VARCHAR2
                                         , flags       IN     NUMBER ) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
                                     , ctx2 IN     ListAggregator ) RETURN NUMBER
 );
 /

CREATE OR REPLACE TYPE BODY ListAggregator IS
   STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ListAggregator) RETURN NUMBER IS
   BEGIN
      sctx := ListAggregator(NULL);
      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateInitialize;

   MEMBER FUNCTION ODCIAggregateIterate (self  IN OUT ListAggregator
                                       , value IN     VARCHAR2 ) RETURN NUMBER IS
   BEGIN
      IF value IS NOT NULL
      THEN
         self.vList := concat(concat(self.vList,', '),value);
      END IF;

      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateIterate;

   MEMBER FUNCTION ODCIAggregateTerminate (self        IN OUT ListAggregator
                                         , returnValue    OUT VARCHAR2
                                         , flags       IN     NUMBER ) RETURN NUMBER IS
   BEGIN
      returnValue := LTrim(self.vList, ', ');
      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateTerminate;

   MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
                                     , ctx2 IN     ListAggregator) RETURN NUMBER IS
   BEGIN
      IF self.vList IS NULL
      THEN
         self.vList := ctx2.vList;
      ELSIF ctx2.vList IS NOT NULL
      THEN
         self.vList := concat(concat(self.vList,', '),ctx2.vList);
      END IF;

      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateMerge;
END;
/

CREATE OR REPLACE FUNCTION LIST(vValue IN VARCHAR2) RETURN VARCHAR2
   PARALLEL_ENABLE
   AGGREGATE
   USING ListAggregator;
/

SELECT deptno
     , LIST(ename)
  FROM emp
 GROUP BY deptno
/

LISTAGG

and finally if you are on ORACLE 11R2, there is a built-in function to do list aggregation

SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename)
 FROM emp
 GROUP BY deptno
 /

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...