I have 3 tables (Potions and Reagents and relation of both) and I want to pivot the results, pass to explain:

TABLE POTIONS
ID    DESCRIPTION
1     Heal
2     Mana
3     Explosion

TABLE REAGENTS
ID   DESCRIPTION
1    Base
2    Mandrake
3    Tulip
4    Sunflower
5    Powder

TABLE POTION_REAGENTS
ID_POTION    ID_REAGENT
1            1
1            3
2            1
2            2
2            5
3            4
3            5

I want to obtain the result like this but I dont know how pivot with dynamic columns

POTION    REAG_1    REAG_2   REAG_3
Heal      Base      Tulip    NULL
Mana      Base      Mandrake Powder
Explosion Sunflower Powder   NULL

I want if new potion have 4 Reagent the select return's REAG_4 column. Its possible?

Thx for your time!

CodePudding user response：

You cannot use a dynamic pivot; you must have a pre-defined set of values in the PIVOT clause. However, you can use the ROW_NUMBER analytic function to pre-generate a numeric index for potion reagents:

SELECT potion,
       reagent1,
       reagent2,
       reagent3,
       reagent4
FROM   (
  SELECT p.id,
         p.description AS potion,
         r.description AS reagent,
         ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY r.description) AS rn
  FROM   potions p
         INNER JOIN potion_reagents pr
         ON (p.id = pr.id_potion)
         INNER JOIN reagents r
         ON (pr.id_reagent = r.id)
)
PIVOT (
  MAX(reagent) FOR rn IN (1 AS reagent1, 2 AS reagent2, 3 AS reagent3, 4 AS reagent4)
)

Which, for your sample data:

CREATE TABLE POTIONS (ID, DESCRIPTION) AS
SELECT 1, 'Heal' FROM DUAL UNION ALL
SELECT 2, 'Mana' FROM DUAL UNION ALL
SELECT 3, 'Explosion' FROM DUAL;

CREATE TABLE REAGENTS (ID, DESCRIPTION) AS
SELECT 1, 'Base' FROM DUAL UNION ALL
SELECT 2, 'Mandrake' FROM DUAL UNION ALL
SELECT 3, 'Tulip' FROM DUAL UNION ALL
SELECT 4, 'Sunflower' FROM DUAL UNION ALL
SELECT 5, 'Powder' FROM DUAL;

CREATE TABLE POTION_REAGENTS (ID_POTION, ID_REAGENT) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 3 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 2, 5 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL;

Outputs:

fiddle

CodePudding user response：

To me it seems you're a making a game or something of a kind. That means there is another "external" programming is being used to process data you're getting from the database.

For that case there is a possibility to obtain a pivot result with dynamical set of columns in XML format. You need the "pivot xml"

select *
 from (select p.description p_desc, 
              r.description r_desc, 
              'REAG_' || row_number() over (partition by p.description order by r.description) reag_num
         from potion_reagents pr
         join reagents r
           on r.id = pr.id_reagent
         join potions p
           on p.id = pr.id_potion)
pivot xml(
   min(r_desc)
   for reag_num in (ANY)
)

As I said, the answer you'll get will be in XML format which is quite simple to parse. Here is an example for "Mana" potion with a fourth reagent I named "Secret"

<PivotSet>
  <item>
    <column name = "REAG_NUM">REAG_1</column>
    <column name = "MIN(R_DESC)">Base</column>
  </item>
  <item>
    <column name = "REAG_NUM">REAG_2</column>
    <column name = "MIN(R_DESC)">Mandrake</column>
  </item>
  <item>
    <column name = "REAG_NUM">REAG_3</column>
    <column name = "MIN(R_DESC)">Powder</column>
  </item>
  <item>
    <column name = "REAG_NUM">REAG_4</column>
    <column name = "MIN(R_DESC)">Secret</column>
  </item>
</PivotSet>

Here's a dbfiddle with an example based on data you've provide us with