Dynamic Query for PIVOT In Clause using PIVOT XML

Learn how to write a dynamic sql query in “for PIVOT In Clause”.

Generally in “for PIVOT In Clause” only constant values are allowed as a list to process the query.

For example

SELECT *
FROM (
SELECT DEPTNO
FROM EMP
)
PIVOT
(
COUNT(*) as detp_employees_cnt FOR
DEPTNO IN(10, 20, 30)
);

Below example restricts the execution of select query in “for PIVOT In Clause”.

SELECT *
FROM (
      SELECT DEPTNO 
      FROM EMP
     ) 
PIVOT
   (
    COUNT(*) as detp_employees_cnt FOR 
    DEPTNO IN(SELECT DEPTNO 
      FROM EMP)
   );

Output:
ORA-00936: missing expression

The above restriction can be solved using PIVOT XML concept.

To pass the dynamic values as a list using select query “for PIVOT In Clause” we can use Pivot XML concept.

Query example with PIVOT XML

SELECT *
FROM (
      SELECT DEPTNO 
      FROM EMP
     ) 
PIVOT XML
   (
    COUNT(*) as detp_employees_cnt FOR 
    DEPTNO IN(SELECT DEPTNO 
      FROM EMP)
   );

When you run the above query you will get the result in XML format as shown below.

<PivotSet>
    <item>
        <column name = "DEPTNO">10</column>
        <column name = "DETP_EMPLOYEES_CNT">3</column>
   </item>
   <item>
        <column name = "DEPTNO">20</column>
        <column name = "DETP_EMPLOYEES_CNT">5</column>
   </item>
   <item>
        <column name = "DEPTNO">30</column>
        <column name = "DETP_EMPLOYEES_CNT">6</column>
   </item>
</PivotSet>

The XML result set is a type of Oracle XML and it seems so difficult for end users to understand.

To understand the end users we have to display value from XML result.

Here is an example to demonstrate how could get value from the first column in XML result set in PIVOT XML query through the specific path.

To get the value from XML Result set we have use extractvalue function.

Syntax:

EXTRACTVALUE(RESULTSET_COLUMN_NAME_xml,'XMLPATH_STRING//text()')

SELECT extractvalue(deptno_xml,'/PivotSet/item[1]/column[2]//text()') deptno10,
   extractvalue(deptno_xml,'/PivotSet/item[2]/column[2]//text()') deptno20,
   extractvalue(deptno_xml,'/PivotSet/item[3]/column[2]//text()') deptno30,
   extractvalue(deptno_xml,'/PivotSet/item[4]/column[2]//text()') deptno40
 FROM (
       SELECT DEPTNO 
       FROM EMP
      ) 
 PIVOT xml
    (
     COUNT(*) as detp_employees_cnt FOR 
     DEPTNO IN(SELECT DEPTNO 
       FROM EMP)
    );

Output