Snowflake split INT row into multiple rows-CodePudding

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?

CodePudding user response：

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 |
 ------------ ------- -----------