Skip to Main Content
  • Questions
  • How to to turn a varchar2 into 'N' rows

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Gary.

Asked: October 20, 2009 - 5:54 am UTC

Last updated: October 20, 2009 - 6:14 am UTC

Version: 10.2

Viewed 1000+ times

You Asked

Hello Tom.

I'm a keen long time reader of your asktom site and have a query which I'm struggling with.

I have a table which is stored like this:

create table T1 (
CMPY CHAR(2),
CODE_ID CHAR(1),
RNGE CHAR(8),
CODE_SIZE number(1),
FROM_TO_CNT number(1),
RNGE_DATA varchar2(240)
)

insert into t1 values('CA','A','L0001',8,1,'L9000   LZ999   ');
insert into t1 values('CA','A','L0002',8,1,'A9000   AZ999   ');
insert into t1 values('CA','A','L0003',8,1,'B9000   BZ999   ');
insert into t1 values('CA','A','L0004',8,1,'C9000   CZ999   ');
insert into t1 values('CA','A','L0005',8,1,'D9000000DZ999999');
insert into t1 values('CA','A','L0006',8,2,'E9000   EZ999   F900    F999    ');
insert into t1 values('CA','B','L0007',6,3,'K00001K00002K00010K00020L00010L00099');

SQL> select * from t1;

CM C RNGE      CODE_SIZE FROM_TO_CNT RNGE_DATA
-- - -------- ---------- ----------- --------------------------------------------------
CA A L0001             8           1 L9000   LZ999
CA A L0002             8           1 A9000   AZ999
CA A L0003             8           1 B9000   BZ999
CA A L0004             8           1 C9000   CZ999
CA A L0005             8           1 D9000000DZ999999
CA A L0006             8           2 E9000   EZ999   F900    F999
CA B L0007             6           3 K00001K00002K00010K00020L00010L00099

7 rows selected.



So the structure of this is that I have this FROM_TO_CNT which tells me how many chunks of CODE_SIZE pairs I will find in my RNGE_DATA.

I really want to select this so that each pair of RNGE_DATA come out on a seperate row. I've tried thinking about pivots but come unstuck relating this to CODE_SIZE and FROM_TO_CNT

I'd like the output to look like (abridged)

CMPY CODE-ID RNGE    FROM           TO
CA   A       L0001   L9000          LZ999
CA   A       L0005   D9000000       DZ999999
CA   A       L0006   E9000          EZ999
CA   A       L0006   F900           F999


etc. With multiple FROM/TO pairs for each row where FROM_TO_CNT > 1

I appreciate that the format of the data is not ideal for this query which will become widely used as part of a view but I need to work with what I have ...

Any assistance you can provide would be much appreciated, thank you.




and Tom said...

ops$tkyte%ORA10GR2> select cmpy, code_id, rnge,
  2         substr( rnge_data, 1+COLUMN_VALUE*2*code_size, code_size ) from_,
  3             substr( rnge_data, 1+code_size+COLUMN_VALUE*2*code_size, code_size ) to_
  4    from t1, table( cast( multiset( select level-1 l from dual connect by level <= T1.FROM_TO_CNT ) as sys.odcinumberlist ) )
  5  /

CM C RNGE     FROM_      TO_
-- - -------- ---------- ----------
CA A L0001    L9000      LZ999
CA A L0002    A9000      AZ999
CA A L0003    B9000      BZ999
CA A L0004    C9000      CZ999
CA A L0005    D9000000   DZ999999
CA A L0006    E9000      EZ999
CA A L0006    F900       F999
CA B L0007    K00001     K00002
CA B L0007    K00010     K00020
CA B L0007    L00010     L00099

10 rows selected.



http://asktom.oracle.com/pls/asktom/asktom.search?p_string=sys.odcinumberlist

for further examples/reading on the 'trick' there.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Gary Cowell, October 20, 2009 - 6:19 am UTC

Thanks very much for such a speedy response Tom. It is, as always, much appreciated.

One day, I will nail pivot queries into my brain and each step takes me a little closer :)