snowflake splitting table A's integer columns based on Table B's split percentage

Question

I will like to do the following in snowflake query. Very new to snowflake , from what I have read it seems Snowflake doesn't support loops

I have 2 tables

Table A
Id   col1   col2   col3
1     ABC    100     50
2     DEF    200      0

Table B
Date        Percentage   Name
2021-04-28       .5       XYZ
2021-04-28       .5       ZYX

In my output query I want to split each row in Table A

the integer columns are split by the Percentage from Table B
Add Name column from Table B (to represent the split integer value against that particular name

Output 
Id   col1   col2   col3    Name
1     ABC     50     25     XYZ
1     ABC     50     25     ZYX
2     DEF    100      0     XYZ
2     DEF    100      0     ZYX

Hi - what have you tried so far? Please update your question with the SQL you have managed to write so far. I'm not sure why you feel you need looping to solve this problem but Snowflake does support looping - by using Stored Procedures — NickW

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-04-28T16:22:31

This seems like a simple cartesian join:

create or replace table A (id number, col1 varchar, col2 number, col3 number);
insert into A values (1,'ABC',100,50),
(2,'DEF',200,0);

create or replace table B ("Date" date, Percentage number(3,2),Name varchar );
insert into B values ('2021-04-28',0.5,'XYZ'),
('2021-04-28',0.5,'ZYX');


select Id, col1, 
round(col2 * percentage) as col2,
round(col3 * percentage) as col3,
Name 
from A, B;


+----+------+------+------+------+
| ID | COL1 | COL2 | COL3 | NAME |
+----+------+------+------+------+
|  1 | ABC  |   50 |   25 | XYZ  |
|  1 | ABC  |   50 |   25 | ZYX  |
|  2 | DEF  |  100 |    0 | XYZ  |
|  2 | DEF  |  100 |    0 | ZYX  |
+----+------+------+------+------+

snowflake splitting table A's integer columns based on Table B's split percentage

1 Answers