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

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

FOR DEPARTMENT IN ('IT' IT,'ACC' ACC,'PURCHASE' PURCHASE)

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

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>
"

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