All Forums Teradata Applications
17 Apr 2016
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

 

dnoeth 4628 posts Joined 11/04
17 Apr 2016

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

 

 

Dieter

19 Apr 2016

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

You must sign in to leave a comment.