title: Snowflake latteral flatten data types
body: <p>I have a table containing an id column and a json column(variant data type). I want to flatten the data, make the <code>value</code> column a <code>variant</code>, assign each value in the <code>value</code> column a data type if a condition is met, then eventually pivot the data and have each column be the correct data type.</p>
  <p>Example code that doesn't work:</p>
  <pre><code>with cte as (
      select 
          1 as id,
          parse_json('{
              &quot;field1&quot;:&quot;TRUE&quot;,
              &quot;field2&quot;:&quot;some string&quot;,
              &quot;field3&quot;:&quot;1.035&quot;,
              &quot;field4&quot;:&quot;097334&quot;
          }') as my_output
  )
  
  select 
      id,
      key,
      to_variant(
          case
              when value in ('true', 'false') then value::boolean
              when value like ('1.0') then value::decimal
              else value::string
          end) as value
  from cte, lateral flatten(my_output)
  </code></pre>
  <p>Ultimately, I'd like to pivot the data and have a wide table with columns <code>id</code>, <code>field1</code>, <code>field2</code>, etc. where <code>field1</code> is <code>boolean</code>, <code>field2</code> is <code>string</code>, <code>field3</code> is a <code>decimal</code> etc.</p>
  <p>This is just a simple example, instead of 4 fields, I'm dealing with hundreds.
  Is this possible?</p>
  <p>For the pivot, I'm using <code>dbt_utils.get_column_values</code> to get the column names dynamically. I'd really prefer a solution that doesn't involve listing out the column names, especially since there are hundreds.</p>