Search code examples
oracle-databaseoracle11goracle12c

How to un-pivot from table in Oracle SQL

I have 1-5 slabs for Progressive discount. The slabs could vary from one transaction to another. I want to store all the five values in 5 variables.

My table is like this.

enter image description here

Expected values are like this

enter image description here

Example:2

If my table is like this

enter image description here

Expected values are like this

enter image description here

Example:3

If my table is like this

enter image description here

Expected values are like this

enter image description here

There can be only one or two slabs as well.

Thanks in advance

Solution

  • This is a multi column pivot not an unpivot: 

    --BUILD Sample dataset called MyTable 
WITH MyTable as (
SELECT 0 slab_from, 100 slab_to, 1 discount FROM dual union all
SELECT 100 slab_from, 200 slab_to, 5 discount FROM dual union all
SELECT 200 slab_from, 99999999 slab_to, 8 discount FROM dual ),

--Now build a CTE with a row number that we can use to pivot the data.
CTE as (SELECT Slab_from, Discount, row_number() over (Order by slab_FROM) RN 
     FROM myTable)

--Now build the data set.  Though I'm a bit confused why there are 4 slabs and 3 discounts in your expected results...

SELECT * FROM (SELECT * FROM CTE)
PIVOT (
max(slab_from) as SLAB, max(Discount) as Discount --[We could add max(Slab_to) SLABTO] to get the max value for each as well if needed...
for RN in (1,2,3,4,5)  --RowNumber values 1-5 since 5 is max according to question
);

    The above gives us: 

    +--------+------------+--------+------------+--------+------------+--------+------------+--------+------------+
| 1_SLAB | 1_DISCOUNT | 2_SLAB | 2_DISCOUNT | 3_SLAB | 3_DISCOUNT | 4_SLAB | 4_DISCOUNT | 5_SLAB | 5_DISCOUNT |
+--------+------------+--------+------------+--------+------------+--------+------------+--------+------------+
|      0 |          1 |    100 |          5 |    200 |          8 |        |            |        |            |
+--------+------------+--------+------------+--------+------------+--------+------------+--------+------------+

    If you want the column names to begin 0 just subtract 1 from the RN in the CTE.

    Possible Answer 2: Tweeked: assuming all slab ranges start at 0 and that 1-5 are reserved for actual rows that may/maynot be in your base table. 

    --BUILD Sample dataset called MyTable 
WITH MyTable as (
SELECT 0 slab_from, 100 slab_to, 1 discount FROM dual union all
SELECT 100 slab_from, 200 slab_to, 5 discount FROM dual union all
SELECT 200 slab_from, 99999999 slab_to, 8 discount FROM dual),

--Now build a CTE with a row number that we can use to pivot the data.
CTE as (SELECT 0 "0_SLAB", Slab_to, Discount, row_number() over (Order by slab_FROM) RN 
     FROM myTable)

--Now build the data set.  Though I'm a bit confused why there are 4 slabs and 3 discounts in your expected results...

SELECT * FROM (SELECT * FROM CTE)
PIVOT (
max(slab_to) as SLAB, max(Discount) as Discount --[We could add max(Slab_to) SLABTO] to get the max value for each as well if needed...
for RN in (1,2,3,4,5)  --RowNumber values 1-5 since 5 is max according to question
);

    The only difference here is I use slab_to and I hardcode 0_SLAB to 0 so all other possible ranges adjust from 1-5.

    Columns are prefixed instead of suffixed but that's how the pivot does it. and they are not in order of all slabs first followed by all discounts; but again; that's how the pivot does it; but column order and name I wouldn't think would matter so long as the data is right and repeatable.

    But I still struggle with why the pivots' needed.. You have the data needed in a row based table that is normalized, extract the data iterate though it in the application and present it I don't know why we need the data to be pivoted.