CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Conor C
Conor C

Reputation: 27

Snowflake split INT row into multiple rows

I have the following data in snowflake

Account ID Ad ID New Value
A1 B1 2
D2 C2 4

I am looking to split the INT column into multiple rows depending on the value in each cell so I am left with the following:

Account ID Ad ID New Value
A1 B1 1
A1 B1 1
D2 C2 1
D2 C2 1
D2 C2 1
D2 C2 1

Can someone please advise how I would go about doing something similar to this?

Upvotes: 0

Views: 481

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

Can you try this one?

select Account_ID,Ad_ID, 1  New_Value
from mytable,
lateral flatten ( SPLIT( REPEAT('x',New_Value - 1), 'x' )  );

+------------+-------+-----------+
| ACCOUNT_ID | AD_ID | NEW_VALUE |
+------------+-------+-----------+
| A1         | B1    |         1 |
| A1         | B1    |         1 |
| D2         | C2    |         1 |
| D2         | C2    |         1 |
| D2         | C2    |         1 |
| D2         | C2    |         1 |
+------------+-------+-----------+

Upvotes: 2

Related Questions