In this post we will discuss how we can concat rows in different ways.
-- Objects used in this Example
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select * from emp;
EMPID FNAME LNAME SEX SSN SALARY DEPTNO
------ -------------------- ------------------------------ --- --------- ---------
501 JOHN DOE M 500000001 45000 4001
502 JOHN SMITH M 500000002 60000 4001
503 SEAN LEE M 500000003 45000 4001
504 EVAN SEAN M 500000004 50000 4002
505 REBECCA SEAN F 500000005 30000 4002
506 TIM DUNCAN M 500000006 30000 4002
507 ROBERT DUVAL M 500000007 30000 4002
508 CLINT JOHNSON M 500000008 30000 4002
516 George BUSH M 500000016 30000 4004
517 SAM MALONE M 30000 4004
518 NANCY REAGAN F 500000018 30000 4004
519 HILLARY CLINTON F 500000019 30000 4004
520 MARRY GEORGIA cat F 500000020 30000 4004
600 SOON CHUN f 500000029 50000 4002
14 rows selected
SQL> select * from dept;
DEPTNO NAME BUILDING
------ -------------------- --------------------
4002 WOMAN CLOTHING BUILDING II
4003 MEN CLOTHING BUILDING II
4004 KITCHEN APPLIANCES MAIN BUILDING
4001 SHOES BUILDING I
4006 FINANCE FOX BUILDING
Using WM_CONCAT
WM_CONCAT is an undocumented oracle aggregate function, which will concatenate the rows (delimited by comma) and returns a single value. WMSYS schema owns this function. As WM_CONCAT is undocumented, Oracle holds the support for this, means, we should not use this function in production ideally.
SQL> SELECT d.name dept_name, wm_concat(fname || ' ' || lname) employee
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.name;
DEPT_NAME EMPLOYEE
-------------------- --------------------------------------------------------------------------------
KITCHEN APPLIANCES SAM MALONE,George BUSH,MARRY GEORGIA,HILLARY CLINTON,NANCY REAGAN
SHOES JOHN SMITH,SEAN LEE,JOHN DOE
WOMAN CLOTHING SOON CHUN,ROBERT DUVAL,EVAN SEAN,REBECCA SEAN,TIM DUNCAN,CLINT JOHNSON
if we are in need of other delimiter instead of "," we can use the translate function.
Using STRAGG
STRAGG is an another undocumented oracle aggregate function similar to WM_CONCAT, except the owner and delimiter. Here there will be no delimiter and the owner of this function is SYS.
SQL> SELECT d.name dept_name,
listagg(fname || ' ' || lname, ',') within GROUP(ORDER BY fname, lname) employee
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.name;
DEPT_NAME EMPLOYEE
-------------------- --------------------------------------------------------------------------------
KITCHEN APPLIANCES George BUSH,HILLARY CLINTON,MARRY GEORGIA,NANCY REAGAN,SAM MALONE
SHOES JOHN DOE,JOHN SMITH,SEAN LEE
WOMAN CLOTHING CLINT JOHNSON,EVAN SEAN,REBECCA SEAN,ROBERT DUVAL,SOON CHUN,TIM DUNCAN
Using LISTAGG
LISTAGG aggregate function is a well documented function released along with oracle 11g database. the advantage of this function is, we can specify the delimited as a parameter to this function.
SQL> SELECT d.name dept_name,
listagg(fname || ' ' || lname, ',') within GROUP(ORDER BY fname, lname) employee
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.name;
DEPT_NAME EMPLOYEE
-------------------- --------------------------------------------------------------------------------
KITCHEN APPLIANCES George BUSH,HILLARY CLINTON,MARRY GEORGIA,NANCY REAGAN,SAM MALONE
SHOES JOHN DOE,JOHN SMITH,SEAN LEE
WOMAN CLOTHING CLINT JOHNSON,EVAN SEAN,REBECCA SEAN,ROBERT DUVAL,SOON CHUN,TIM DUNCAN
Using sys_connect_by_path
SQL> SELECT NAME,
2 ltrim(MAX(sys_connect_by_path(fname||' '||lname, ','))
3 keep(dense_rank LAST ORDER BY curr), ',') AS employees
4 FROM (SELECT NAME,
5 fname,lname,
6 row_number() over(PARTITION BY NAME ORDER BY fname,lname) AS curr,
7 row_number() over(PARTITION BY NAME ORDER BY fname,lname) - 1 AS prev
8 FROM emp e,dept d WHERE e.deptno = d.deptno)
9 GROUP BY NAME
10 CONNECT BY prev = PRIOR curr
11 AND NAME = PRIOR NAME
12 START WITH curr = 1;
NAME EMPLOYEES
-------------------- --------------------------------------------------------------------------------
KITCHEN APPLIANCES George BUSH,HILLARY CLINTON,MARRY GEORGIA,NANCY REAGAN,SAM MALONE
SHOES JOHN DOE,JOHN SMITH,SEAN LEE
WOMAN CLOTHING CLINT JOHNSON,EVAN SEAN,REBECCA SEAN,ROBERT DUVAL,SOON CHUN,TIM DUNCAN
