Multiple rows into a comma delimited list in PL/SQL

by Andy
Posted on February 20, 2018March 13, 2018

Multiple rows

One common thing to request is data from multiple rows into a comma delimited list in PL/SQL. If you are using Oracle 11g or greater there are a couple of options. The first is pretty verbose and difficult to follow but it is here for reference and posterity.

SELECT SUBSTR(SYS_CONNECT_BY_PATH (COLUMN_NAME, ','). 2) CSV
     FROM (SELECT COLUMN_NAME, ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) RN,
          COUNT(*) OVER () CNT
          FROM YOUR_TABLE)
      WHERE RN = CNT
      START WITH RN = 1
      CONNECT BY RN = PRIOR RN + 1

SELECT SUBSTR(SYS_CONNECT_BY_PATH (COLUMN_NAME, ','). 2) CSV

FROM (SELECT COLUMN_NAME, ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) RN,

COUNT(*) OVER () CNT

FROM YOUR_TABLE)

WHERE RN = CNT

START WITH RN = 1

CONNECT BY RN = PRIOR RN + 1

LISTAGG

Kind of messy and a little hard to follow. There is a new option in 11g that makes things a lot cleaner and easier.

SELECT COLUMN_NAME1, <strong>LISTAGG</strong>(COLUMN_NAME2, ',') 
WITHIN GROUP (ORDER BY COLOUMN_NAME1)
FROM YOUR_TABLE

SELECT COLUMN_NAME1, <strong>LISTAGG</strong>(COLUMN_NAME2, ',')

WITHIN GROUP (ORDER BY COLOUMN_NAME1)

FROM YOUR_TABLE

That is a lot cleaner and easier to understand. The new LISTAGG function makes taking multiple rows into comma delimited list in PL/SQL much easier and faster.