CODEWITHSUNDEEP
sqlteradatarowsbreak
10k
10k

Reputation: 17

Break ranges into rows Teradata

I have an input like this:

Start     End
1         100

I would link to break each range into rows:

ID
1
2
3
4
.
.
.
100

I'm trying to get this output in TERADATA, can you guys help me?

Thanks a lot.

Upvotes: 1

Views: 227

Answers (2)

dnoeth
dnoeth

Reputation: 60513

Teradata's proprietary EXPAND ON syntax is for creating time series, but can used for your task, too. Assuming TD16.20+ this can be further simplified using a time series function:

If start and end are positiv INTegers:

select t.*
   ,TD_TIME_BUCKET_NUMBER(time '00:00:00.000000+00:00', begin(pd), microseconds(1)) - 1 -- TD 16.20
from mytable as t
expand on -- works on date/time only -> convert int to period
   period(time '00:00:00.000000+00:00' + (interval '0.000001' second * start_)
         ,time '00:00:00.000001+00:00' + (interval '0.000001' second * end_)
         ) as pd

For BIGINT it's a bit more complex and must be nested:

select dt.*
   ,start_ + rownum - 1
from
 (
   select t.*
      ,TD_TIME_BUCKET_NUMBER(timestamp '0001-01-01 00:00:00.000000+00:00', begin(pd), microseconds(1) ) as rownum
   from mytable as t
   expand on -- works on date/time only -> convert int to period
      period(timestamp '0001-01-01 00:00:00.000000+00:00'
            ,timestamp '0001-01-01 00:00:00.000001+00:00' + (interval '0.000001' second * (end_ - start_))
            ) as pd 
 ) as dt
 ;

This allows creating up to 9,999,999,999 rows, if you need more (up to 863,999,999,999,999) you might switch to an INTERVAL DAY TO SECOND, but then you should better rethink you data model :-)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

One method is a recursive CTE:

with recursive cte (n, end) as (
      select start, end
      from t
      union all
      select n + 1, end
      from cte
      where n < end
     )
select n
from cte;

Note: You can include other columns in cte, so you can break up each row and still have other information such as an id or whatever.

Upvotes: 1

Related Questions