sqlpivotsnowflake-cloud-data-platformunpivot

Unpivot multiple columns in Snowflake


I have a table that looks as follows: enter image description here

I need to unpivot the Rating and the Comments as follows: enter image description here

What is the best way to do this in Snowflake?

Note: there are some cells in the comment columns that are NULL

Adding details:

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;

will produce produce the desired results, but with data that has NULLs, the unpivoted rows that have NULLs go missing from the output:

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

Solution

  • If all you need to solve is for the table specified in the question - you can do it manually with a set of UNION ALL:

    select NAME
      , 'ACTING_RATING' as SKILL, ACTING_RATING as SKILL_RATING, ACTING_COMMENTS as SKILL_COMMENTS
    from DATA
    union all
    select NAME
      , 'COMEDY_RATING', COMEDY_RATING, COMEDY_COMMENTS
    from DATA
    union all
    select NAME
      , 'MUSICAL_PERFORMANCE_RATING', MUSICAL_PERFORMANCE_RATING, MUSICAL_PERFORMANCE_COMMENTS
    from DATA