0

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

Sivabalanarayanan L
  • 104
  • 11
  • so is this 0 based start or 1? Discount is 1 slab is 0... – xQbert Jun 03 '19 at 12:39
  • You also have overlap Is slab_to of 100 in discount 1 or 5? – xQbert Jun 03 '19 at 12:44
  • Slabs should overlap. Slab_to of 100 is 1. – Sivabalanarayanan L Jun 03 '19 at 13:55
  • Possible duplicate of [Using pivot on multiple columns of an Oracle row](https://stackoverflow.com/questions/23939244/using-pivot-on-multiple-columns-of-an-oracle-row) – xQbert Jun 03 '19 at 15:25
  • Why sin't there a 4th discount if there are 4 slabs? – xQbert Jun 03 '19 at 15:26
  • The number of slab could vary from one transaction to another. It could have 5 rows for a transaction. As per rule, it should be from 1 to 5 rows. – Sivabalanarayanan L Jun 03 '19 at 18:01
  • I get that: but each slab should have it's own discount right? So your example shows 3 rows/records. However expected results show 4 slabs and 3 discounts. I would expect to see 3 slabs given 3 data rows.. My updated answer should be close; I just need to understand why you have 4 slabs – xQbert Jun 03 '19 at 18:03
  • And... if all 5 rows were present. what happens then to the max(slab_to) value it gets ignored? – xQbert Jun 03 '19 at 18:11
  • No. That's Slab5. I need to capture that as well. – Sivabalanarayanan L Jun 04 '19 at 06:53
  • I'm unable to assist any further. I don't understand the requirements well enough at this stage. It makes no sense to me from a pattern standpoint why you have 4 slabs in your example and 3 discounts given you only have 3 records in your sample data. I see no effort in explaining why this is. – xQbert Jun 04 '19 at 11:47
  • I have edited my question to explain it further. – Sivabalanarayanan L Jun 04 '19 at 13:16
  • I still don't know why in example 1 you have 4 slabs and only 3 discounts when only 3 records exist in sample data. Why 4 SLABS? you only have 3 RECORDS! However, moving on; why doesn't the proposed answer work? What's wrong with it. – xQbert Jun 04 '19 at 14:12

1 Answers1

1

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.

xQbert
  • 34,733
  • 2
  • 41
  • 62