"But I want to get all the data in a single column?"
This is called string aggregation.
See these threads:
Re: Concat rows values into single column (and a sys_connect_by_path example further up)
Re: Multiple rows into a single line in 'Single Column Table'
LISTAGG 11g function
Various string aggregation techniques
Tom Kytes string aggregation
Note: recommendations to use the wm_concat function should be considered poor suggestions as it is an undocumented function, not supported and subject to change in future versions of Oracle. Even Tom Kyte says so... Re: DISTINCT not working with wmsys.wm_concat
select LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) from a;
If you are asking for version 9i.. Then try the below
SELECT SUBSTR(XMLAGG(XMLELEMENT(a,','||name).EXTRACT('//text()') ORDER BY name),2) comma_val FROM a;
Here is another way.
FROM (SELECT NAME,
ROW_NUMBER()OVER(ORDER BY NAME) RN
START WITH RN=1
CONNECT BY PRIOR RN=RN-1
-- AND PRIOR NAME!=NAME (you can include this if you want to avoid duplicates)
AND PRIOR SYS_GUID() IS NOT NULL;