title: Lateral flatten two columns without repetition in snowflake
body: <p>I have a query that groups by a two variables to get a total of another. In order to maintain my table structure for later computations I listagg() two other variables to save for the next stage of the query. However, when I attempt to do two later flatten's of the listagg() columns my data is repeated to many times.</p>
  
  <p>Example: my_table</p>
  
  <pre><code>   id   |     list1       | code|   list2  | total
  --------|-----------------|-----|----------|---
  2434166 | 735,768,769,746 | 124 | 21,2,1,6 | 30
  
  
  select
  id,
  list1_table.value::int as list1_val,
  code,
  list2.value::int as list2_val,
  total
  
  from my_table
  lateral flatten(input=&gt;split(list1, ',')) list1_table,
  lateral flatten(input=&gt;split(list2, ',')) list2_table
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>   id   |     list1       | code|   list2  | total
  --------|-----------------|-----|----------|---
  2434166 |      768        | 124 |     2    | 30
  2434166 |      735        | 124 |     2    | 30
  2434166 |      746        | 124 |     2    | 30
  2434166 |      769        | 124 |     2    | 30
  2434166 |      768        | 124 |     21   | 30
  2434166 |      735        | 124 |     21   | 30
  2434166 |      746        | 124 |     21   | 30
  2434166 |      769        | 124 |     21   | 30
  2434166 |      768        | 124 |     6    | 30
  2434166 |      735        | 124 |     6    | 30
  2434166 |      746        | 124 |     6    | 30
  2434166 |      769        | 124 |     6    | 30
  2434166 |      768        | 124 |     1    | 30
  2434166 |      735        | 124 |     1    | 30
  2434166 |      746        | 124 |     1    | 30
  2434166 |      769        | 124 |     1    | 30
  </code></pre>
  
  <p>I understand what is going on but I'm just wonder how do I get my desired result:</p>
  
  <pre><code>   id   |     list1       | code|   list2  | total
  --------|-----------------|-----|----------|---
  2434166 |      768        | 124 |     2    | 30
  2434166 |      735        | 124 |     21   | 30
  2434166 |      746        | 124 |     6    | 30
  2434166 |      769        | 124 |     1    | 30
  </code></pre>