Monday, May 14, 2012

Oracle 10g:Get All Query output values in Comma Separated in One Single row

Replace ColumnName to Column that need out in Comma Separated.

Replace TableName with your Table 

SELECT SUBSTR (c, 2) concatenated
  FROM (SELECT     SYS_CONNECT_BY_PATH ( ColumnName, ',') c, r
              FROM (SELECT   ROWNUM ID, ColumnName,
                             RANK () OVER (ORDER BY ROWID DESC) r
                        FROM TableName
                        WHERE ColumnName='Value'
                    ORDER BY ColumnName)
        START WITH ID = 1
        CONNECT BY PRIOR ID = ID - 1)
 WHERE r = 1;


Dear User,

Thank you very much for your kind response