Multiple rows into single comma separated string.



--Convreting multiple rows in to single comma separated string without looping trough the cursor.
--Using SQL with Oracle SYS_CONNECT_BY_PATH to concatenate multiple rows in to single line.


SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(your_col, ', ')), ',') AS YOUR_COL
FROM (
SELECT 'X' AS X, t1.name_col AS YOUR_COL,
ROW_NUMBER () OVER (PARTITION BY 'X' ORDER BY ROWNUM) RN
FROM
table_1 t1,
table_2 t2,
WHERE
t1.id = t2.id
)
CONNECT BY 'X' = PRIOR 'X'
AND RN = PRIOR RN + 1
START WITH RN = 1

You can view this article online at:
http://www.kiranreddys.com/knowledgebase/index.php/article/multiple-rows-into-single-comma-separated-string