Oracle: concatenating strings from multiple rows in one table-field

Developer had the requirement to concat a field from multiple rows as one single string in one row.
Something like this:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

Desired Output:

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Looked like a not so unusually request to me. But digging the docs and the web I found that until 11g there is no official solution for this.

WM_CONCAT

If the WorkspaceManager-schema WMSYS is installed, which normally is if using the DBCA for db-creation in 10g, one has the WMCONCAT()-function that easily does what we want:

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

WM_CONCAT has no built-in functionality to change the separator. But if your data doesn’t prevent it, you could wrap WMCONCAT in a REPLACE like this as a workaround:

select deptno, replace(wm_concat(ename),',',';') as employees 
from emp 
group by deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------
        10 KING;CLARK;MILLER
        20 JONES;ADAMS;SCOTT;SMITH;FORD
        30 BLAKE;WARD;MARTIN;JAMES;TURNER;ALLEN

In this example the default-delimiter comma (“,”) is replaced by a semicolon (“;”) after all concatenation is done.

But beware that WM_CONCAT is unofficial and unsupported by Oracle. So no alternative for production-systems.

LISTAGG

In Oracle Database 11g there is the new LISTAGG()-function as the official way to do it:

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

As you see, with LISTAGG it is even possible to define the delimiter. It is the second parameter of the LISTAGG-function (here it is set to comma “,”).

I found an interesting comparison between WMCONCAT and LISTAGG here.

Custom Function

Unfortunately my production-db is neither 11g nor I have the WMSYS-schema installed.
But I found a pretty solution on www.oracle-base.com which you can find here. The approach uses 10g’s COLLECT-function, a table-type and a user-function to get the desired output.
This snippet is from the named article on oracle-base:

    CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
    /

    CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                              p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
      l_string     VARCHAR2(32767);
    BEGIN
      FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
        IF i != p_varchar2_tab.FIRST THEN
          l_string := l_string || p_delimiter;
        END IF;
        l_string := l_string || p_varchar2_tab(i);
      END LOOP;
      RETURN l_string;
    END tab_to_string;
    /

The query below shows the COLLECT function in action.

    COLUMN employees FORMAT A50

    SELECT deptno,
           tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
    FROM   emp
    GROUP BY deptno;
           
        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLARK,KING,MILLER
            20 SMITH,JONES,SCOTT,ADAMS,FORD
            30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

As obvious from the code above, this custom-made concat-function also offers the possibility to define a delimiter:

    SELECT deptno,
           tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab),':') AS employees
    FROM   emp
    GROUP BY deptno;

        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLAR:KING:MILLER
            20 SMITH:JONES:SCOTT:ADAMS:FORD
            30 ALLEN:WARD:MARTIN:BLAKE:TURNER:JAMES

In this example I set the delimiter to ‘:’ as the second parameter of the tab_to_string-function:

tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab),’:’))

And if you have the need to get your strings ordered by something, you could “order by” in the COLLECT-function like “COLLECT(ename order by ename)”:

tab_to_string(CAST(COLLECT(ename order by ename) AS t_varchar2_tab),’:’))
Advertisements

One Response to Oracle: concatenating strings from multiple rows in one table-field

  1. Aftab says:

    very helpful information…
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: