title: Creating a new row where values between columns are different
body: <p>I'm trying to create a new row when values between two columns are different.</p>
  <p>Data sample:</p>
  <pre><code>SELECT * FROM SOURCE_TABLE
  
  Info   Date          Observed    Boolean
  QWE    2020-03-08    2020-03-08  true
  QWE    2020-03-14    2020-03-16  true         
  QWE    2020-03-20    2020-03-20  false
  </code></pre>
  <p>I'm trying to write a statement that would make a new row when the <code>Date</code> and <code>Observed</code> columns have a different value. In the final table I'd keep only the <code>Date</code> column, without <code>Observed</code>, and have a new <code>Boolean_date</code> and <code>Boolean_observed</code> columns with true/false values.
  Other columns would stay the same value (keep the value from the row where the two columns differ).</p>
  <p>So the new result would look like this:</p>
  <pre><code>Info   Date          Observed     Boolean_date  Boolean_observed
  QWE    2020-03-08    2020-03-08   true          true
  QWE    2020-03-14    2020-03-16   true          false
  QWE    2020-03-16    2020-03-16   false         true
  QWE    2020-03-20    2020-03-20   false         false
  </code></pre>
  <p>In SQL Server I would use CROSS APPLY in this case, but that is not available in Snowflake.
  I was thinking something along the lines of:</p>
  <pre><code>SELECT
      ...
      CASE 
          WHEN Boolean = true AND &quot;Date&quot; = Observed THEN Boolean_date = true AND Boolean_observed = true
          WHEN Boolean = false AND &quot;Date&quot; = Observed THEN Boolean_date = false AND Boolean_observed = false
          WHEN Boolean = true AND &quot;Date&quot; &lt;&gt; Observed THEN (INSERT NEW ROW)
  
      END
  FROM SOURCE_TABLE
  </code></pre>
  <p>If you have any other approach that would work better, please suggest it.</p>