hi all
SCOTT.@.testdb > select * from (Select deptno, sal from emp)
2 PIVOT XML (
3 sum(sal) as salary FOR deptno IN (SELECT DISTINCT deptno FROM emp)
4 );
DEPTNO_XML
------------------------------------------------------------------------------------------------------------------------------------
<PivotSet><item><column name = "DEPTNO">10</column><column name = "SALARY">8750</column></item><item><column name = "DEPTNO">20</col
umn><column name = "SALARY">10875</column></item><item><column name = "DEPTNO">30</column><column name = "SALARY">9400</column></ite
m></PivotSet>
when trying to get data into rows/column, it is not showing, i wrote as below, where i am making mistake, please guide.
for query below, got help from: https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql
with xml_tab as (select * from (Select deptno, sal from emp)
PIVOT XML (
sum(sal) as salary FOR deptno IN (SELECT DISTINCT deptno FROM emp)
)
)
SELECT xt.*
FROM xml_tab x,
XMLTABLE('/PivotSet/item'
PASSING x.deptno_xml
COLUMNS
"DEPTNO" NUMBER PATH 'deptno',
"SALARY" NUMBER PATH 'salary'
) xt
SCOTT.@.testdb > /
DEPTNO SALARY
---------- ----------
regards.