rows convert in oracle sql

Question

I have values like below

1,a,b,c
2,d,e
3,f,g

Expected output

1 a
1 b
1 c
2 d
2 e                 

Can you please help me?

Answer 1

It is very much close to implementing the logic to Split comma delimited strings in a table. The only tricky thing is that you have the row number along with the string itself.

You could use ROWNUM as pseudo column, and then filter out those rows where the leading substr of the string is repeating with the ROWNUM.

For example,

Setup

SQL> CREATE TABLE t(text VARCHAR2(4000));

Table created.

SQL>
SQL> INSERT INTO t SELECT '1,a,b,c' text FROM dual;

1 row created.

SQL> INSERT INTO t SELECT '2,d,e' text FROM dual;

1 row created.

SQL> INSERT INTO t SELECT '3,f,g' text FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM t;

TEXT
----------
1,a,b,c
2,d,e
3,f,g

SQL>

Solution:

SQL> WITH DATA AS(
  2  SELECT ROWNUM rn, text FROM t
  3  )
  4  SELECT *
  5  FROM
  6    (SELECT rn,
  7      trim(regexp_substr(t.text, '[^,]+', 1, lines.COLUMN_VALUE)) text
  8    FROM DATA t,
  9      TABLE (CAST (MULTISET
 10      (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
 11      ) AS sys.odciNumberList ) ) lines
 12    )
 13  WHERE TO_CHAR(rn) <> text
 14  ORDER BY rn
 15  /

        RN TEXT
---------- ----------
         1 a
         1 b
         1 c
         2 d
         2 e
         3 f
         3 g

7 rows selected.

SQL>