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.
Best Answer
Using the code from this link as a starting point, this should give you the result without the need for a
DISTINCT
:See SQL Fiddle with Demo