How to replicate the values

Teradata Applications
Enthusiast

How to replicate the values

Hi All,

I want to Write a query to duplicate each row based on the value in the repeat column? The input table data looks like as below

Products, Repeat
----------------
A,         3
B,         5
C,         2

Now in the output data, the product A should be repeated 3 times, B should be repeated 5 times and C should be repeated 2 times. The output will look like as below

Products, Repeat
----------------
A,        3
A,        3
A,        3
B,        5
B,        5
B,        5
B,        5
B,        5
C,        2
C,        2
Tags (1)
2 REPLIES
Senior Apprentice

Re: How to replicate the values

There are several ways to get this result:


#1: classical SQL would be a JOIN to a number-table with sequential values ON n BETWEEN 1 AND Repeat, depending on the actula data might need a lot of CPU

#2: a Recursive Select, effcient as long as Repeat is a small number

#3: Abusing EXPAND ON, proprietary syntax for PERIOD, but most efficient:

SELECT *
FROM tab
EXPAND ON PERIOD(DATE '2000-01-01', DATE '2000-01-01' + repeat_) AS pd
Enthusiast

Re: How to replicate the values

Hi Dieter,

Can u please explain how can we do it using recursive query.