Search code examples
sqlteradatateradata-sql-assistantteradatasql

How to duplicate table row based on column value in Teradata?

Can someone help this task? If I have one table as below in Teradata:

caseid content repeat
id1 row1 2
id2 row2 3

I want to build a new table based on the repeat value as below. How should I do?

caseid content repeat groupid
id1 row1 2 1
id1 row1 2 2
id2 row2 3 1
id2 row2 3 2
id2 row2 3 3

Thanks!

Solution

  • Teradata proprietary EXPAND ON syntax creates time series and can be used for this task:

    SELECT t.*
   -- convert period back to int
  ,End(pd) - Current_Date AS groupid
FROM mytable AS t
-- works on date/time only -> convert int to period
EXPAND ON PERIOD(Current_Date, Current_Date + repeat) AS pd