Getting around Oracle Dynamic Pivot issue using XML

Question

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.

So you want the query to return various columns based on data in a table. Check this out https://stackoverflow.com/questions/50259728/oracle-dynamic-pivoting/52468571#52468571 — Dr Y Wit, Jun 12 '19 at 14:16
@DrYWit Not really because I tried that solution but I won't work because for `XMLTABLE` function you have to pass the `COLUMNS` clause and the columns you want to see however in my case I think the best solution for me is to create an XML element, populate it and return it. I will update my question ! — Fourat, Jun 12 '19 at 15:30

score 1 · Answer 1 · answered Jun 12 '19 at 05:35

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

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.

thank your for your answer, but I don't think this is a good solution for me because what `PIVOT` function really does is that it creates a string query and executes it. The purpose of my question was is there any other way (mostly using XML). — Fourat, Jun 12 '19 at 07:20

Getting around Oracle Dynamic Pivot issue using XML

1 Answers1