Multiple rows to single column with comma separator using DB2 SQL

With the help of Repeater we can display multiple columns to single row in Cognos. The same can be achieved in SQL also.

This article helps you to align multiple rows to single column with the help of primary key/unique id.

For Example:
Data format in table:

DEPT_ID        EMP_NAME
11                    Alan
11                    Balan
11                    Mary
12                    Steve
12                    Rosy
13                    Mike
13                    Santa

Requirement is given below:

DEPT_ID        EMP_NAME
11                    Alan, Balan, Mary
12                    Steve, Rosy
13                    Mike, Santa

To achieve the above requirement, we can use recursive function. Below recursive SQL may helps you to get multiple rows to single column with comma as separator.

WITH L1(DEPT_ID,EMP_NAME,RN_SEQ)  AS
 (
SELECT   C.DEPT_ID,
         (C.EMP_NAME),
         C.RN_SEQ
FROM     (
 SELECT   DEPT_ID,
          CAST(EMP_NAME AS VARCHAR(10000))EMP_NAME,
          ROW_NUMBER() OVER(PARTITION BY DEPT_ID ORDER BY EMP_NAME) RN_SEQ
 FROM     <SCHEMA>.<TABLENAME>) C
 WHERE    C.RN_SEQ = 1          
 UNION ALL   
           
 SELECT   L2.DEPT_ID,
          L2.EMP_NAME ||','|| C.EMP_NAME,
          C.RN_SEQ
 FROM     (
   SELECT   DEPT_ID,
            CAST(EMP_NAME AS VARCHAR(100))EMP_NAME,
            ROW_NUMBER() OVER(PARTITION BY DEPT_ID) RN_SEQ
   FROM     <SCHEMA>.<TABLENAME>)C,
            L1 AS L2
 WHERE    L2.DEPT_ID = C.DEPT_ID
 AND      L2.EMP_NAME <> C.EMP_NAME
 AND      L2.RN_SEQ+1 = C.RN_SEQ           )
 SELECT   DEPT_ID,
          REPLACE(EMP_NAME,'.','')
 FROM     L1 AS L3
 WHERE    RN_SEQ = (
   SELECT   MAX(L4.RN_SEQ)
   FROM     (
   SELECT   DEPT_ID,
            CAST(EMP_NAME AS VARCHAR(100))EMP_NAME,
            ROW_NUMBER() OVER(PARTITION BY DEPT_ID ORDER BY EMP_NAME) RN_SEQ
   FROM     <SCHEMA>.<TABLENAME>) L4
   WHERE    L4.DEPT_ID = L3.DEPT_ID
   GROUP BY L4.DEPT_ID)