Tuesday 1 June 2021

How to convert single column into row in PL SQL

CREATE TABLE sale_records
(
   sale_id       NUMBER PRIMARY KEY,
   fiscal_year    NUMBER,
   quantity1      NUMBER,
   quantity2      NUMBER,
   quantity3      NUMBER
); 

INSERT INTO sale_records(sale_id, fiscal_year, quantity1, quantity2, quantity3)
VALUES(1,2019, NULL, 100, 200);
 
INSERT INTO sale_records(sale_id, fiscal_year, quantity1, quantity2, quantity3)
VALUES(2,2020, 350, NULL, 450);
 
INSERT INTO sale_records(sale_id, fiscal_year, quantity1, quantity2, quantity3)
VALUES(3,2021,  580, 680, NULL);

SELECT * FROM sale_records

Query output:
---------------- 
SALE_ID        FISCAL_YEAR          QUANTITY1  QUANTITY2            QUANTITY3
1                      2019                                                      100                             200
2                      2020                             350                                                      450
3                      2021                             580                   680      

SELECT * FROM sale_records
UNPIVOT INCLUDE NULLS(
    quantity_value
    FOR quantity
    IN (
        quantity1 AS 'quantity1',
        quantity2 AS 'quantity2',
        quantity3 AS 'quantity3'
    )
);

Query output:
----------------
SALE_ID        FISCAL_YEAR         QUANTITY            QUANTITY_VALUE
1                      2019                            quantity1        
1                      2019                            quantity2                 100
1                      2019                            quantity3                 200
2                      2020                            quantity1                 350
2                      2020                            quantity2        
2                      2020                            quantity3                 450
3                      2021                            quantity1                 580
3                      2021                            quantity2                 680
3                      2021                            quantity3