How to convert single column to multiple rows in Oracle

Example 1 ->  Take the below Example :

CREATE TABLE config_value

(

   Config_id    VARCHAR2(40)

 , config_code    VARCHAR2(100)

);

INSERT INTO config_value

     VALUES (1, 'AB, CS,HJ,KL,PO,UI');

Commit;

Select * from config_value;’

Config_Id	Config_Code
1	AB, CS,HJ,KL,PO,UI

Expected output – Now we want the output as

Config_code	Config_id
AB	1
CS	1
HJ	1
KL	1
PO	1
UI	1
AB	1
CS	1

Solution :

SELECT TRIM (REGEXP_SUBSTR (config_code

                          , '[^,]+'

                          , 1

                          , lvl))

          config_code

     , Config_id

  FROM (    SELECT config_code, LEVEL lvl, Config_id

              FROM config_value

        CONNECT BY LEVEL <=

                        LENGTH (config_code)

                      - LENGTH (REPLACE (config_code, ',', ''))

                      + 1);

Example 2   PIVOT Operator

The PIVOT operator takes data in separate rows, aggregates it and converts it into columns.  Lets take an example :

CREATE TABLE test_pivot

(

   consumer_id   NUMBER

 , product_id    NUMBER

 , qty           NUMBER

);

INSERT INTO test_pivot VALUES (123, 7, 67);

INSERT INTO test_pivot VALUES (566, 2, 23);

INSERT INTO test_pivot VALUES (522, 3, 44);

INSERT INTO test_pivot VALUES (125, 88, 60);

INSERT INTO test_pivot VALUES (189, 8, 188);

    

INSERT INTO test_pivot VALUES (123, 7, 167);

INSERT INTO test_pivot VALUES (566, 2, 123);

INSERT INTO test_pivot VALUES (522, 3, 144);

INSERT INTO test_pivot VALUES (125, 88, 160);

INSERT INTO test_pivot VALUES (189, 8, 188);

    

INSERT INTO test_pivot VALUES (123, 8, 67);

INSERT INTO test_pivot VALUES (566, 3, 23);

INSERT INTO test_pivot VALUES (522, 2, 44);

INSERT INTO test_pivot VALUES (125, 7, 60);

INSERT INTO test_pivot VALUES (189, 88, 188);  

COMMIT;

Data will look like as:

SELECT * FROM test_pivot;

Now use pivot Operator to sum the qty

SELECT *

  FROM (SELECT product_id, qty FROM test_pivot) 

        PIVOT (SUM (qty) AS sum_qty

 FOR (product_id) IN (7, 2, 3, 88, 8));

Now Let’s print consumer Id as well:

SELECT *

  FROM (SELECT consumer_id ,product_id, qty 

          FROM test_pivot) 

 PIVOT (SUM(qty) AS sum_qty FOR (product_id)IN (7, 2, 3, 88, 8));

Limitations :  For Pivot queries, pivot values should be know well ahead of time.

We cannot put select distinct <column_name > from <table_name>,