CODEWITHSUNDEEP
oracle-databasepivotoracle12c
Fourat
Fourat

Reputation: 2447

Getting around Oracle Dynamic Pivot issue using XML

I have an existing table like :

CREATE TABLE ES_DEVISES (
  NOM VARCHAR2(500),
  DEVISE VARCHAR2(5),
  ORDRES NUMBER(5,0)
)
-- For testing purposes :
INSERT INTO ES_DEVISES VALUES ('ES1','CHF',157);
INSERT INTO ES_DEVISES VALUES ('ES1','USD',1328);
INSERT INTO ES_DEVISES VALUES ('ES2','AUD',5);
INSERT INTO ES_DEVISES VALUES ('ES1','AUD',23);
INSERT INTO ES_DEVISES VALUES ('ES1','CNY',17);
INSERT INTO ES_DEVISES VALUES ('ES1','INR',17);
INSERT INTO ES_DEVISES VALUES ('ES2','CNY',1);
INSERT INTO ES_DEVISES VALUES ('ES2','INR',4);
INSERT INTO ES_DEVISES VALUES ('ES2','USD',218);
INSERT INTO ES_DEVISES VALUES ('ES2','CHF',42);

And I have that pivots rows to columns like this, that returns the desired output :

SELECT * FROM ES_DEVISES
PIVOT (
  MAX(ORDRES) FOR DEVISE IN ('USD' USD,'CHF' CHF,'CNY' CNY,'INR' INR,'AUD' AUD)
);

-- Output :
NOM                  USD        CHF        CNY        INR        AUD
---------------- ---------- ---------- ---------- ---------- ----------
ES1                1328        157         17         17         23 
ES2                218         42          1          4          5

Now, with some evolution the table ES_DEVISES will contain random units (ES1, ES2, ES3, ...) and random currencies (USD, EUR, XRP, BTC, ...) so the query above won't be valid anymore. I found this answer that recommends using PIVOT XML so the query became :

SELECT NOM, DEVISE_XML
FROM ES_DEVISES
PIVOT XML(
  MAX(ORDRES) FOR DEVISE IN (SELECT DEVISE FROM NEW_TABLE_FOR_CURRENCIES)
) t;

-- Output :
NOM              DEVISE_XML                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ES1              <PivotSet><item><column name = "DEVISE">AUD</column><column name = "MAX(ORDRES)">23</column></item><item><column name = "DEVISE">CHF</column><column name = "MAX(ORDRES)">157</column></item><item><column name = "DEVISE">CNY</column><column name = "MAX(ORDRES)">17</column></item><item><column name = "DEVISE">INR</column><column name = "MAX(ORDRES)">17</column></item><item><column name = "DEVISE">USD</column><column name = "MAX(ORDRES)">1328</column></item></PivotSet>    
ES2              <PivotSet><item><column name = "DEVISE">AUD</column><column name = "MAX(ORDRES)">5</column></item><item><column name = "DEVISE">CHF</column><column name = "MAX(ORDRES)">42</column></item><item><column name = "DEVISE">CNY</column><column name = "MAX(ORDRES)">1</column></item><item><column name = "DEVISE">INR</column><column name = "MAX(ORDRES)">4</column></item><item><column name = "DEVISE">USD</column><column name = "MAX(ORDRES)">218</column></item></PivotSet>

The next step now is to parse that XML so I can get back to a result set that looks like the first output. The problem now is that the DEVISE_XML field doesn't contain the NOM field and I couldn't find a way to parse the XML to dynamic columns, I tried to use XMLTABLE but it gets me back to something like SELECT * FROM ES_DEVISES.

My question is : is there any way to either :

1) return an XML looking like this :

<PivotSet>
    <row>
        <unit>ES1</unit>
        <column name="AUD">23</column>
        <column name="CHF">157</column>
        <column name="CNY">17</column>
        <column name="INR">17</column>
        <column name="USD">1328</column>
    </row>
    <row>
        <unit>ES2</unit>
        <column name="AUD">218</column>
        <column name="CHF">42</column>
        <column name="CNY">1</column>
        <column name="INR">4</column>
        <column name="USD">5</column>
    </row>
</PivotSet>

2) Parse the XML to return the desired output (which should look like the output from the first query) dynamically.

PS : I saw many solutions in the net like using LISTAGG or building the query with a string and executing it with EXECUTE IMMEDIATE. But none of that interest me because I could just easily do it with JAVA but the aim of this question is to find a clear, easily maintainable query that's doing all the job from a PROCEDURE.

Upvotes: 1

Views: 844

Answers (1)

Popeye
Popeye

Reputation: 35920

I am using dynamic PIVOT function for the such requirement in my projects.

I have added one more row, which is random. -- As per your requirement.

CREATE TABLE ES_DEVISES (
  NOM VARCHAR2(500),
  DEVISE VARCHAR2(5),
  ORDRES NUMBER(5,0)
);
-- For testing purposes :
INSERT INTO ES_DEVISES VALUES ('ES1','CHF',157);
INSERT INTO ES_DEVISES VALUES ('ES1','USD',1328);
INSERT INTO ES_DEVISES VALUES ('ES2','AUD',5);
INSERT INTO ES_DEVISES VALUES ('ES1','AUD',23);
INSERT INTO ES_DEVISES VALUES ('ES1','CNY',17);
INSERT INTO ES_DEVISES VALUES ('ES1','INR',17);
INSERT INTO ES_DEVISES VALUES ('ES2','CNY',1);
INSERT INTO ES_DEVISES VALUES ('ES2','INR',4);
INSERT INTO ES_DEVISES VALUES ('ES2','USD',218);
INSERT INTO ES_DEVISES VALUES ('ES2','CHF',42);
INSERT INTO ES_DEVISES VALUES ('ES3','RNDM',100); -- random record added by me


SELECT
    *
FROM
    TABLE ( PIVOT(' select NOM
                      ,      DEVISE
                      ,      MAX(ORDRES) ORDRES
                      from   ES_DEVISES
                      group
                      by     NOM
                      ,      DEVISE
                    '
    ) );

OUTPUT

Output

Here, PIVOT is the function and I got it from AMIS.

Here is the link to download the function: Dynamic PIVOT

Extract the downloaded zip and execute the script pivotFun.sql to create the PIVOT function in your DB.

Hope, You will like it.

Upvotes: 1

Related Questions