Hi All,
Can you please tell me if we can achieve below requirement using only PIVOT.
table in data:
| Item | Loc | Item_Loc_Date | Qty |
| I1 | L1 | 19-Oct-15 | 10 |
| I1 | L1 | 20-Oct-15 | 30 |
| I1 | L1 | 26-Oct-15 | 5 |
| I1 | L2 | 19-Oct-15 | 8 |
| I1 | L2 | 26-Oct-15 | 15 |
| I1 | L2 | 30-Oct-15 | 12 |
Expected Output:
| Item | Loc | 19-Oct-15 | 20-Oct-15 | 21-Oct-15 | 22-Oct-15 | 23-Oct-15 | 24-Oct-15 | 25-Oct-15 | 26-Oct-15 | 27-Oct-15 | 28-Oct-15 | 29-Oct-15 | 30-Oct-15 |
| I1 | L1 | 10 | 30 | null | null | null | null | null | 5 | null | null | null | null |
| I1 | L2 | 8 | null | null | null | null | null | null | 15 | null | null | null | 12 |
that is pick the minimum and maximum date in the table and set them as column names for every item and loc combination.
In the above case for Item I1 and Loc L1 minimum date is 19-Oct-2015 and maximum date is 26-Oct-2015
In the above case for Item I1 and Loc L2 minimum date is 19-Oct-2015 and maximum date is 30-Oct-2015
So with the data is there in system minimum date is 19-Oct-2015 and maximum date is 30-Oct-2015.
in future more item and loc combinations will be adding and dates also may vary.
so am looking if we can get the dynamic columns based on the data with PIVOT in oracle.
WITH T1
AS (SELECT 'I1' ITEM,
'L1' LOC,
TO_DATE ('19-OCT-2015', 'DD-MON-RRRR') ITEM_LOC_DATE,
10 QTY
FROM DUAL
UNION ALL
SELECT 'I1' ITEM,
'L1' LOC,
TO_DATE ('20-OCT-2015', 'DD-MON-RRRR') ITEM_LOC_DATE,
30 QTY
FROM DUAL
UNION ALL
SELECT 'I1' ITEM,
'L1' LOC,
TO_DATE ('26-OCT-2015', 'DD-MON-RRRR') ITEM_LOC_DATE,
5 QTY
FROM DUAL
UNION ALL
SELECT 'I1' ITEM,
'L2' LOC,
TO_DATE ('19-OCT-2015', 'DD-MON-RRRR') ITEM_LOC_DATE,
8 QTY
FROM DUAL
UNION ALL
SELECT 'I1' ITEM,
'L2' LOC,
TO_DATE ('26-OCT-2015', 'DD-MON-RRRR') ITEM_LOC_DATE,
15 QTY
FROM DUAL
UNION ALL
SELECT 'I1' ITEM,
'L2' LOC,
TO_DATE ('30-OCT-2015', 'DD-MON-RRRR') ITEM_LOC_DATE,
12 QTY
FROM DUAL)
SELECT *
FROM T1;