rows to columns snowflake

Question

i am struggling this one and any help will be appreciate. I am on Snowflake and i have a table with the following data

Project	WBS_ID	Level
P.0051458	P.0051458.01	2
P.0051458	P.0051458.02	2
P.0051458	P.0051458.03	2
P.0051478	P.0051478.01	2

I need this to be pivoted to something like this

Project	Level_01	Leve_02	Leve_03
P.0051458	P.0051458.01	P.0051458.02	P.0051458.03
P.0051478	P.0051478.01

any help would be appreciated

Answer 1

Assuming that the LEVEL column is what should be used to pivot (which does not match your sample data, but would make more sense), we can use conditional aggregation:

select project,
    max(case when level = 1 then wbs_id end) as level_1,
    max(case when level = 2 then wbs_id end) as level_2,
    max(case when level = 3 then wbs_id end) as level_3
from mytable
group by project

As opposed to vendor-specific PIVOT features, the syntax of conditional aggregation is supported in most databases - and it is also more flexible.