Multiple rows into a comma delimited list in PL/SQL
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.
comma delimited sql
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
WHERE RN = CNT
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1
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)
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.