multiply (clone) rows based on number
Hello All,
I need to multiply (clone) rows based on number specified in the source row, i.e. if I have N = 8, then I need to clone current row 8 times. Below you will see the code that does this using time TIMESERIES. However the code looks pretty complicated. Can anyone advise a better/simpler/optimum way of doing such numbber-based row cloning?
WITH C AS
(
SELECT 1 AS ID, 5 AS N
UNION ALL
SELECT 2 AS ID, 8 AS N
UNION ALL
SELECT 3 AS ID, 2 AS N
)
SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY the_date) AS RN FROM
(
SELECT ID, ts::date the_date
FROM
(
SELECT ID, '2019-01-01 00:00:00'::TIMESTAMP as tm FROM C
UNION ALL
SELECT ID, TIMESTAMPADD (DD, N - 1, '2019-01-01 00:00:00'::TIMESTAMP) FROM C
) as t
TIMESERIES ts as '1 DAY' OVER (PARTITION BY ID ORDER BY t.tm)
) a
ORDER BY 1, 2
;
Answers
That's one way. Sequence generator UDTs (User-Defined Transforms) are also very popular.