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 properly then use the following command to restrict the column length.
COLUMN ENAMES FORMAT A50
Happy Learning!!!!!!!
Comments