Home » Developer & Programmer » JDeveloper, Java & XML » Help in Query: XML output to Columns using XMLTABLE (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Help in Query: XML output to Columns using XMLTABLE [message #676624] Tue, 25 June 2019 02:39
deepakmannazhi
Messages: 136
Registered: February 2010
Location: Dubai, UAE
Senior Member

Dear Experts,

I am new to XML functions in SQL


Sample table

CREATE TABLE TEST_PIVOT_TAB (ID NUMBER, DEPARTMENT VARCHAR2(30), SAL NUMBER(20));

REM INSERTING into test_pivot_tab
 
Insert into "test_pivot_tab" (ID,DEPARTMENT,SAL) values (1,'IT',2000);
Insert into "test_pivot_tab" (ID,DEPARTMENT,SAL) values (2,'ACC',3000);
Insert into "test_pivot_tab" (ID,DEPARTMENT,SAL) values (3,'ACC',5000);
Insert into "test_pivot_tab" (ID,DEPARTMENT,SAL) values (4,'PURCHASE',2000);
Insert into "test_pivot_tab" (ID,DEPARTMENT,SAL) values (5,'IT',2500);
Insert into "test_pivot_tab" (ID,DEPARTMENT,SAL) values (6,'PURCHASE',2000);


ID	DEPARTMENT	  SAL
------------------------------------------
1	IT	           2000
2	ACC	           3000
3	ACC	           5000
4	PURCHASE	   2000
5	IT	           2500
6	PURCHASE	   2000


I used PIVOT QUERY to get following output

SELECT * FROM(SELECT ID, DEPARTMENT,SAL FROM TEST_PIVOT_TAB)
PIVOT(SUM(SAL)  FOR DEPARTMENT IN ('IT' IT,'ACC' ACC,'PURCHASE' PURCHASE))
order by 1

ID	IT	ACC	PURCHASE
1	2000		
2		3000	
3		5000	
4			2000
5	2500		
6			2000


instead of using constant values
FOR DEPARTMENT IN ('IT' IT,'ACC' ACC,'PURCHASE' PURCHASE)

I used PIVOT XML

CREATE TABLE TEST_XML_TAB AS
SELECT * FROM(SELECT ID, DEPARTMENT,SAL FROM TEST_PIVOT_TAB)
PIVOT XML(SUM(SAL) SAL FOR DEPARTMENT IN ( SELECT DISTINCT DEPARTMENT FROM TEST_PIVOT_TAB ))

select *  from test_xml_tab

Got below output.

ID	DEPARTMENT_XML
1	"<PivotSet>
  <item>
    <column name="DEPARTMENT">ACC</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">IT</column>
    <column name="SAL">2000</column>
  </item>
  <item>
    <column name="DEPARTMENT">PURCHASE</column>
    <column name="SAL"/>
  </item>
</PivotSet>
"
2	"<PivotSet>
  <item>
    <column name="DEPARTMENT">ACC</column>
    <column name="SAL">3000</column>
  </item>
  <item>
    <column name="DEPARTMENT">IT</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">PURCHASE</column>
    <column name="SAL"/>
  </item>
</PivotSet>
"
3	"<PivotSet>
  <item>
    <column name="DEPARTMENT">ACC</column>
    <column name="SAL">5000</column>
  </item>
  <item>
    <column name="DEPARTMENT">IT</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">PURCHASE</column>
    <column name="SAL"/>
  </item>
</PivotSet>
"
4	"<PivotSet>
  <item>
    <column name="DEPARTMENT">ACC</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">IT</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">PURCHASE</column>
    <column name="SAL">2000</column>
  </item>
</PivotSet>
"
5	"<PivotSet>
  <item>
    <column name="DEPARTMENT">ACC</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">IT</column>
    <column name="SAL">2500</column>
  </item>
  <item>
    <column name="DEPARTMENT">PURCHASE</column>
    <column name="SAL"/>
  </item>
</PivotSet>
"
6	"<PivotSet>
  <item>
    <column name="DEPARTMENT">ACC</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">IT</column>
    <column name="SAL"/>
  </item>
  <item>
    <column name="DEPARTMENT">PURCHASE</column>
    <column name="SAL">2000</column>
  </item>
</PivotSet>
"


Could you please suggest me a method to get output in below format


ID	IT	ACC	PURCHASE
1	2000		
2		3000	
3		5000	
4			2000
5	2500		
6			2000




Thanks and Regards
Deepak



[Updated on: Wed, 26 June 2019 00:59]

Report message to a moderator

Previous Topic: how do I modify XML with a data from a query?
Next Topic: Huge Batch execution through the java application into the oracle DB
Goto Forum:
  


Current Time: Mon Dec 09 07:16:05 CST 2019