Julie karen Julie karen - 1 month ago 8
SQL Question

Converting coma separated values in multiple column to rows

I am trying to create a view from a table which will convert coma separated values in multiple column to rows in Oracle.

I have done this successfully for one column. but I am not able to do it for two or three columns.

I used following script which runs successfully for one column.

Create VIEW MULTITESET AS
SELECT rownum AS ID1,Tagging.COMMENTS,Tagging.category,Tagging.STATUS,
trim(regexp_substr(Tagging.OBJ_ID, '[^,]+', 1, lines.column_value)) OBJ_ID
FROM Tagging,
TABLE (CAST (MULTISET
(SELECT LEVEL FROM dual
CONNECT BY instr(Tagging.OBJ_ID, ',', 1, LEVEL - 1) > 0
) AS sys.odciNumberList ) ) lines
ORDER BY id, lines.column_value


Now I need to do the same for a second column named
OBJname
as like for column
OBJ_ID
.

So I tried something stupid as like following which won't work.

Create VIEW MULTITESET AS
SELECT rownum AS ID1,Tagging.COMMENTS,Tagging.category,Tagging.STATUS,
trim(regexp_substr(Tagging.OBJ_ID, '[^,]+', 1, lines.column_value)) OBJ_ID
FROM Tagging,
TABLE (CAST (MULTISET
(SELECT LEVEL FROM dual
CONNECT BY instr(Tagging.OBJ_ID, ',', 1, LEVEL - 1) > 0
) AS sys.odciNumberList ) ) lines
ORDER BY id, lines.column_value ,
trim(regexp_substr(Tagging.OBJname , '[^,]+', 1, lines.column_value)) OBJname
FROM Tagging,
TABLE (CAST (MULTISET
(SELECT LEVEL FROM dual
CONNECT BY instr(Tagging.OBJname , ',', 1, LEVEL - 1) > 0
) AS sys.odciNumberList ) ) lines
ORDER BY id, lines.column_value


In nut shell i wanted tabel-A in the picture below to be converted to table-B. How can I do that?

See attached picture

My actual view query is:

SELECT rownum AS TRACKID2, LEAPFROG_TAGGING.ID, LEAPFROG_TAGGING.CREATED_DATE,
LEAPFROG_TAGGING.CREATED_BY, LEAPFROG_TAGGING.COMMENTS,
leapfrog_tagging.category, LEAPFROG_TAGGING.STATUS, LEAPFROG_TAGGING.OBJ_NAME,
trim(regexp_substr(LEAPFROG_TAGGING.OBJ_ID, '[^,]+', 1, lines.column_value)) OBJ_ID
FROM LEAPFROG_TAGGING,
TABLE (CAST (MULTISET (
SELECT LEVEL FROM dual
CONNECT BY instr(LEAPFROG_TAGGING.OBJ_ID, ',', 1, LEVEL - 1) > 0
) AS sys.odciNumberList ) ) lines
ORDER BY id, lines.column_value;

Answer

Assuming the number of comma-separated values always matches in the two columns (so exactly one name per ID), and that you're on 11gR2, you can use recursive subquery factoring:

create view multiteset as
with t (obj_id, obj_name, status, other_columns, rn, orig_obj_id, orig_obj_name) as (
  select regexp_substr(obj_id, '[^,]+', 1, 1),
    regexp_substr(obj_name, '[^,]+', 1, 1),
    status, other_columns, 1, obj_id, obj_name
  from tagging
  union all
  select regexp_substr(orig_obj_id, '[^,]+', 1, rn + 1),
    regexp_substr(orig_obj_name, '[^,]+', 1, rn + 1),
    status, other_columns, rn + 1, orig_obj_id, orig_obj_name
  from t
  where rn < regexp_count(orig_obj_id, '[^,]+')
)
select obj_id, obj_name, status, other_columns
from t;

select * from multiteset;

OBJ_ID                       OBJ_NAME STATUS OTHER_
---------------------------- -------- ------ ------
1                            a        open   value1
1                            a        closed value2
4                            hj       na     value3
2                            s        open   value1
2                            s        closed value2
5                            hj       na     value3
3                            d        open   value1
6                            hj       na     value3
4                            f        open   value1
7                            hj       na     value3

10 rows selected. 

The column names in your question as a bit inconsistent so you'll have to adjust for your real names.

The anchor members gets the first element from each list, and keeps a copy of the original lists, and a counter to see which element we're on (starting with 1).

The recursive member then looks for the next (rn +) element in each list, passing the rest of the value along unchanged, apart from the counter.

The final query against the CTE then just gets the generated values, ignoring the temporary ones the CTE was keeping track of (rn, orig_obj_id, etc.)

From the new original view definition you added in comments, you might really want:

create view multiteset as
with t (id, created_date, created_by, comments, category, status,
  obj_name, obj_id, rn, orig_obj_name, orig_obj_id)
as (
  select id, created_date, created_by, comments, category, status,
    regexp_substr(obj_name, '[^,]+', 1, 1),
    regexp_substr(obj_id, '[^,]+', 1, 1),
    1, obj_name, obj_id
  from leapfrog_tagging
  union all
  select id, created_date, created_by, comments, category, status,
    regexp_substr(orig_obj_name, '[^,]+', 1, rn + 1),
    regexp_substr(orig_obj_id, '[^,]+', 1, rn + 1),
    rn + 1, orig_obj_name, orig_obj_id
  from t
  where rn < regexp_count(orig_obj_id, '[^,]+')
)
select id, created_date, created_by, comments, category, status,
  obj_name, obj_id
from t;

Also notice there is no order by clause; that doesn't really make sense in a view definition, and should be applied when you query the view instead:

select * from multiteset order by id, obj_id;

If you want to keep the position marker so the view resulsts can be ordered by that as well then include rn (or a more meaningful name!) in the final select list, e.g:

...
select id, rn as trackid2, created_date, created_by, comments, category, status,
  obj_name, obj_id
from t;

which would let you:

select * from multiteset order by id, trackid2;
Comments