PIVOT Table using XML

Rows to Column

I want to get output of multiple rows into single variable seperated by comma. How can I achieve that. There is also requirement of using dynamic sql as table and column will be known at runtime only.

Here is the example :

select rtrim(dbms_xmlgen.getxmltype('select empno '','' e from EMP WHERE DEPTNO = '&p_deptno).extract('//text()'),',') x
from dual;

Enter value for p_deptno: 10

X
--------------
7782,7839,7934



Columns to Rows
I've got comma seperated values in single row, now I need the output in multiple rows. How can I get this?






EMPNO
------
7782
7839
7934

3 rows selected.