I have a table that looks as follows:
I need to unpivot the Rating and the Comments as follows:
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
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