All Forums General
santoshkasturi 1 post Joined 01/13
04 Feb 2013
Converting Range values in a column into Separate rows

Hi,
Could you please help me in converting Range values in a column into Separate rows
There is a table with three columns with below data values.

Col A

Col B

Col C

123

abc

U7 38-40 GEORGE STREET

Col C is a combination of Unit number + Street number + Street name
If the Street number is a range value (as indicated above 38-40) it needs to split in sepearate row data values as below.
 

Col A

Col B

Col C

123

abc

U7 38 GEORGE STREET

123

abc

U7 39 GEORGE STREET

123

abc

U7 40 GEORGE STREET

Thanks
 

Qaisar Kiani 337 posts Joined 11/05
06 Feb 2013

This SQL could be your starting point converting the range values into separte rows and tune it according to your requirements...

CREATE SET TABLE tbl ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      col VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( col );


INSERT INTO tbl VALUES  ( '7-17');
INSERT INTO tbl VALUES  ( '33-39');
INSERT INTO tbl VALUES  ( '1-10');

--TABLE WITH MIN AND MAX:

CREATE SET TABLE tbl2 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      col2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      _min INTEGER,
      _max INTEGER)
PRIMARY INDEX ( col2 );

INSERT INTO tbl2 
SEL col, SUBSTR(col,1,INDEX (col,'-')-1) _min, SUBSTR (col,INDEX(col,'-')+1,CHARS(col)) _max FROM tbl

--RECURSIVE QUERY:

WITH RECURSIVE cte
(col, _add, _min, _max) AS
(
  SELECT -- Current value 
    col2,
    0 AS _add, 
    _min,
    _max
  FROM tbl2
  UNION ALL
  SELECT -- Rest of Values 
    cte.col,
    cte._min+1 AS _add,
                cte._min+1,
                cte._max
  FROM cte JOIN tbl2 s
  ON cte.col = s.col2
  WHERE cte._min <  cte._max
)
SELECT
  cte.col,
  cte._min AS _result
FROM cte JOIN tbl2 s
  ON cte.col = s.col2
ORDER BY  1,2
amerbaig 7 posts Joined 07/11
15 Jul 2013

Why it doesnt work for larger ranges e.g. 50000?

You must sign in to leave a comment.