There are several times that in a project we need comma delimited column names of a record, below is an example on how to get it quickly!!!

Example on PS_JOB:
SELECT RECNAME,
MAX (LTRIM (SYS_CONNECT_BY_PATH (FIELDNAME, ‘,’), ‘,’)) keystring
FROM (SELECT RECNAME,
FIELDNAME,
ROW_NUMBER () OVER (PARTITION BY RECNAME ORDER BY FIELDNAME)
rownumber
FROM PSRECFIELDALL
WHERE RECNAME = ‘JOB’)
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber – 1 AND PRIOR RECNAME = RECNAME
GROUP BY RECNAME

Advertisements