4

My data is like:

keycol,col1,col2,col3
1,a;b;c,some data,some other data
2,x,some data,some other data
3,y;z,some data,some other data

Where a column is delimited in the source system with semicolons.

And I want to pivot it to:

1,a,some data,some other data
1,b,some data,some other data
1,c,some data,some other data
2,x,some data,some other data
3,y,some data,some other data
3,z,some data,some other data

I found a technique here, but I can't quite get it to work:

CREATE TABLE yt
    (keycol int, col1 varchar2(5), col2 varchar2(9), col3 varchar2(15))
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (1, 'a;b;c', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (2, 'x', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (3, 'y;z', 'some data', 'some other data')
SELECT * FROM dual
;

I thought I could include the keycol in the CONNECT BY this way to get parallel recursive chains, but I guess it doesn't work like that. I'm pretty sure I've done this with recursive CTEs in SQL Server.

SELECT keycol
  ,trim(regexp_substr(col1, '[^;]+', 1, level)) col1
  ,col2 
  ,col3
FROM yt t
CONNECT BY keycol = PRIOR keycol AND instr(col1, ';', 1, level - 1) > 0

http://sqlfiddle.com/#!4/3d378

FWIW, I'm on Oracle 10g.

3

Using the code from this link as a starting point, this should give you the result without the need for a DISTINCT:

select t.keycol,
    regexp_substr ( t.col1, '[^;]+', 1, n) as col1,
    t.col2,
    t.col3
from yt t 
cross join
(
    select level n
    from 
    (
        select max(length(col1) - length(replace(col1, ';'))) as max_semi
        from yt
    )
    connect by level <= 1 + max_semi
) c
where c.n <= 1 + length(col1) - length(replace(col1, ';'))
order by t.keycol, col1;

See SQL Fiddle with Demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.