How to convert multiple columns into row in PL SQL

 
CREATE TABLE sales_records
(
   sales_id       NUMBER PRIMARY KEY,
   fiscal_year    NUMBER,
   quantity1      NUMBER,
   quantity2      NUMBER,
   amount1        NUMBER,
   amount2        NUMBER
);

INSERT INTO sales_records(sales_id, fiscal_year, quantity1, quantity2, amount1, amount2)
VALUES(1, 2020, 100, 200, 1000, 2000);
 
INSERT INTO sales_records(sales_id, fiscal_year, quantity1, quantity2, amount1, amount2)
VALUES(2, 2021, 300, 400, 3000, 4000);

SELECT * FROM  sales_records

SALES_ID FISCAL_YEAR  QUANTITY1  QUANTITY2   AMOUNT1     AMOUNT2
1                 2020                     100                  200                    1000                2000
2                 2021                     300                  400                    3000                4000

SELECT sales_id, fiscal_year,rownumber,quantity, amount
FROM sales_records
UNPIVOT (
    (quantity, amount)
    FOR rownumber
    IN (
        (quantity1, amount1) AS '1',
        (quantity2, amount2) AS '2'       
    )
);

SALES_ID      FISCAL_YEAR         ROWNUMBER            QUANTITY    AMOUNT
1                      2020                            1                                      100                  1000
1                      2020                            2                                      200                  2000
2                      2021                            1                                      300                  3000
2                      2021                            2                                      400                  4000

 

How to convert comma separated values into column in PL SQL

SELECT     REGEXP_SUBSTR(:p_string,

                         '[^,]+',

                         1,

                         LEVEL)

              AS site

FROM       DUAL

CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(:p_string, '[^,]*')) + 1;

 

 

SELECT     REGEXP_SUBSTR('1001,1002,1003',

                         '[^,]+',

                         1,

                         LEVEL)

              AS site

FROM       DUAL

CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('1001,1002,1003', '[^,]*')) + 1;