my table contain data in below format and does not contain any automated primary key

Asked Dec 02 '15 at 13:10

Active Feb 24 '16 at 10:15

Viewed 31 times

My table named color_code looks like this:

color_id      color_name
---------     ----------
101           (red,blue,green)
102           (cyan,magenta)
103           (white,deep,light,yellow)
104           (null)

But My data should be in below format and this resultant data should inserted into color_insert table

This should be my output:

color_id        color_name
---------       ------------
101              red
101              blue
101              green
102              cyan
102              magenta
103              white
103              deep
103              light
103              yellow
104

I have tried this, but not satisfied with my logic. Could you please help me on this

DECLARE
BEGIN
  FOR i IN 1..4
  LOOP
    INSERT INTO color_insert
      SELECT color_id,
             TRIM(REGEXP_SUBSTR(color_name, '[^,]+', 1, i)) color_name
        FROM color_test 
        CONNECT BY REGEXP_SUBSTR(color_id, '[^,]+', 1, level) IS NOT NULL
       ORDER BY color_id;
  END LOOP;
END;

edited Feb 24 '16 at 10:15

diziaq

6,881
16
54
96

asked Dec 02 '15 at 13:10

Ranjan Jena

1

Whats your question? – Theunis Dec 02 '15 at 13:11
1

I understand your wish, comma separated values is a mess and should never be allowed in a table. – jarlh Dec 02 '15 at 13:11
how we will achieve below format data in sql or plsql – Ranjan Jena Dec 02 '15 at 13:12
Maybe try and add another field that becomes your primary key, for example `color_code_id`. What have you tried so far? – Theunis Dec 02 '15 at 13:14
See [**Split comma delimited strings in a table in Oracle**](http://lalitkumarb.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/) – Lalit Kumar B Dec 02 '15 at 13:30
I posted an answer in the duplicate post that will almost exactly fit your needs if you change the table and field names. – Jared Dec 02 '15 at 14:23

my table contain data in below format and does not contain any automated primary key

0 Answers0