Strange solution for someone who is looking into this statement for the first time. I have used XMLAGG, XMLELEMENT and EXTRACT functions. See if you can crack it. XMLAGG is an aggregate/group function which aggregates all the values for a column. XMLELEMENT is a function which creates XML tags with tag element E. EXTRACT function then removes all the XML text and retains only text. SELECT deptno, XMLAGG (XMLELEMENT (e, ename || ',')).EXTRACT ('//text()') as ENAMES FROM emp GROUP BY deptno; This statement will give you an output as below, If you observe in this output, there is an extra comma at the end of the ENAMEs list. To remove this, I shall use another function RTRIM which is used to trim characters on the right side. SELECT deptno, RTRIM(XMLAGG (XMLELEMENT (e, ename || ',')).EXTRACT ('//text()'),',') as ENAMES FROM emp GROUP BY deptno; In case if the output does not show the values proper
Mithun Ashok's Experience, Summary, Trainings and Knowledge Sharing on Oracle Database, Oracle Applications, Fusion Middleware, SQL, PL/SQL and Database Testing