This is a frequently asked question “How to display multiple rows in a single row as a delimited string”. It is basically string aggregation.
I will demonstrate the various techniques of string aggregation in Oracle SQL using the standard SCOTT.EMP table.
** LISTAGG enhancements in 12.2 release explained in the end.
- Oracle database 9i and up:
ROW_NUMBER() and SYS_CONNECT_BY_PATH
SQL> SELECT deptno,
2 LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
3 KEEP (DENSE_RANK LAST ORDER BY rn),',') AS emp_name
4 FROM
5 (SELECT deptno,
6 ename,
7 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn,
8 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 rn_lag
9 FROM emp
10 )
11 GROUP BY deptno
12 CONNECT BY rn_lag = PRIOR rn
13 AND deptno = PRIOR deptno
14 START WITH rn = 1
15 ORDER BY deptno;
DEPTNO EMP_NAME
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
- Oracle database 10g and up:
MODEL clause
SQL> select deptno
2 , rtrim(ename,',') enames
3 from ( select deptno
4 , ename
5 , rn
6 from emp
7 model
8 partition by (deptno)
9 dimension by (row_number() over
10 (partition by deptno order by ename) rn
11 )
12 measures (cast(ename as varchar2(40)) ename)
13 rules
14 ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
15 )
16 )
17 where rn = 1
18 order by deptno
19 /
DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
- Oracle database 11g Release 2 and up:
LISTAGG
SQL> SELECT deptno,
2 LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS emp_name
3 FROM emp
4 GROUP BY deptno
5 ORDER BY deptno;
DEPTNO EMP_NAME
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
- LISTAGG Function Enhancements in Oracle Database 12c Release 2 (12.2)
Prior to 12.2, when the concatenation exceeds the limit of return datatype of LISTAGG, it throws following error:
ORA-01489: result of string concatenation is too long
In 12.2, the default behavior is similar to following clause:
ON OVERFLOW ERROR
To handle the overflow error gracefully, you could use following clause:
ON OVERFLOW TRUNCATE
For example,
SELECT deptno,
LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~') WITHIN GROUP (ORDER BY ename)
You could remove the COUNT by using following clause:
WITHOUT COUNT
For example,
SELECT deptno,
LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~' WITHOUT COUNT) WITHIN GROUP (ORDER BY ename)
Hope it helps!