CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Ali Alghaithi
Ali Alghaithi

Reputation: 11

Snowflake: Repeating rows based on column value

How to repeat rows based on column value in snowflake using sql.

I tried a few methods but not working such as dual and connect by.

I have two columns: Id and Quantity.

For each ID, there are different values of Quantity.

Upvotes: 0

Views: 2193

Answers (2)

Adrian White
Adrian White

Reputation: 1804

Ok let's start by generating some data. We will create 10 rows, with a QTY. The QTY will be randomly chosen as 1 or 2.

enter image description here

Next we want to duplicate the rows with a QTY of 2 and leave the QTY =1 as they are.

Obviously you can change all parameters above to suit your needs - this solution works super fast and in my opinion way better than table generation.

Simply stack SPLIT_TO_TABLE(), REPEAT() with a LATERAL() join and voila.

enter image description here

WITH TEN_ROWS AS (SELECT ROW_NUMBER()OVER(ORDER BY NULL)SOME_ID,UNIFORM(1,2,RANDOM())QTY FROM TABLE(GENERATOR(ROWCOUNT=>10)))

SELECT 
   TEN_ROWS.* 
FROM 
   TEN_ROWS,LATERAL SPLIT_TO_TABLE(REPEAT('hire me $10/hour',QTY-1),'hire me $10/hour')ALTERNATIVE_APPROACH;

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

So if you have a count, you can use a generator:

with ten_rows as (
    select row_number() over (order by null) as rn
    from table(generator(ROWCOUNT=>10))
), data(id, count) as (
    select * from values 
    (1,2),
    (2,4)
)
SELECT   
   d.*
   ,r.rn
from data as d
join ten_rows as r
    on d.count >= r.rn
order by 1,3;
ID COUNT RN
1 2 1
1 2 2
2 4 1
2 4 2
2 4 3
2 4 4

Upvotes: 1

Related Questions