CREATE TABLE sale_records
(
sale_id NUMBER PRIMARY KEY,
fiscal_year NUMBER,
quantity1 NUMBER,
quantity2 NUMBER,
quantity3 NUMBER
);
(
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);
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 QUANTITY31 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'
)
);
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
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
No comments:
Post a Comment