I needed to concatenate the multiple rows of a query into a string. I could have used a function to do the same but I need to do that via SQL on a ORACLE database.
Note: This has got a limit of 4000 characters. Good to use in subqueries where you are trying to concatenate less no. of rows. SYS_CONNECT_BY_PATH is only available in Oracle 9i version or later. For earlier versions you need to write a function to do the same.