title: Unpivot multiple columns in Snowflake
body: <p>I have a table that looks as follows:
  <a href="https://i.stack.imgur.com/pl51s.png" rel="noreferrer"><img src="https://i.stack.imgur.com/pl51s.png" alt="enter image description here" /></a></p>
  <p>I need to unpivot the Rating and the Comments as follows:
  <a href="https://i.stack.imgur.com/ZY2Jp.png" rel="noreferrer"><img src="https://i.stack.imgur.com/ZY2Jp.png" alt="enter image description here" /></a></p>
  <p>What is the best way to do this in Snowflake?</p>
  <p>Note: there are some cells in the comment columns that are <code>NULL</code></p>
  <p>Adding details:</p>
  <pre><code>create or replace table reviews(name varchar(50), acting_rating int, acting_comments text, comedy_rating int, comedy_comments text);
  
  insert into reviews values
      ('abc', 4, NULL, 1, 'NO'),
      ('xyz', 3, 'some', 1, 'haha'),
      ('lmn', 1, 'what', 4, NULL);
      
      select * from reviews;
      
  
  
  select name, skill, skill_rating, comments
      from reviews
      unpivot(skill_rating for skill in (acting_rating,  comedy_rating)) 
      unpivot(comments for skill_comments in (acting_comments,comedy_comments)) 
  
  --Following where clause is added to filter the irrelevant comments due to multiple unpivots
  
  where substr(skill,1,position('_',skill)-1) = substr(skill_comments,1,position('_',skill_comments)-1) 
       order by name;
  </code></pre>
  <p>will produce produce the desired results, but with data that has NULLs, the unpivoted rows that have NULLs go missing from the output:</p>
  <pre><code>NAME    SKILL   SKILL_RATING    COMMENTS
  abc COMEDY_RATING   1   NO
  lmn ACTING_RATING   1   what
  xyz ACTING_RATING   3   some
  xyz COMEDY_RATING   1   haha
  </code></pre>