Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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