Snowflake - How to pivot 2 or multiple columns without aggregation

Question

I am new to snowflake and I have a scenario with table having below data,

Combined1 Combined2 Common_code
Name       John      ABC123
City       NY        ABC123
Sex        M         ABC123
Country    USA       ABC123
Name       John      ABC123
City       BF        ABC123
Sex        M         ABC123
Country    USA       ABC123
Name       Lucy      XYZ456
City       CB        XYZ456
Sex        F         XYZ456
Country    UK        XYZ456

So that I want to transpose the table so that the result would look like

Name City Sex Country Common_code
John NY   M   USA     ABC123
John BF   M   USA     ABC123
Lucy CB   F   UK      XYZ456

I have tried multiple approaches using PIVOT, FLATTEN but couldn't crack this up. Main condition is I don't want to lose any record as per the above example. Any response or solution is highly appreciated in Snowflake.

Answer 1

so using a CTE for the data:

with data(Combined1, Combined2, Common_code) as (
    select *
    from values
        ('Name', 'John', 'ABC123'),
        ('City', 'NY', 'ABC123'),
        ('Sex', 'M', 'ABC123'),
        ('Country', 'USA', 'ABC123'),
        ('Name', 'John', 'ABC123'),
        ('City', 'BF', 'ABC123'),
        ('Sex', 'M', 'ABC123'),
        ('Country', 'USA', 'ABC123'),
        ('Name', 'Lucy', 'XYZ456'),
        ('City', 'CB', 'XYZ456'),
        ('Sex', 'F', 'XYZ456'),
        ('Country', 'UK', 'XYZ456')
)

so here is the most likely "optimal" aggregation version that you want to avoid, albeit it, you have duplicate values in your common_code, so this data appears suspect.

select
    max(iff(Combined1='Name',Combined2,null)) as Name
    ,max(iff(Combined1='City',Combined2,null)) as City
    ,max(iff(Combined1='Sex',Combined2,null)) as Sex
    ,max(iff(Combined1='Country',Combined2,null)) as Country
    ,Common_code
from data
group by Common_code
order by Common_code;

But the above relies on the "common code" being distinct per thing of interest. Which it is not, thus the two rows.

Which means we can just do it the classic worst way possible, and let the DB build all combinations, and then throw most the work away, way:

select distinct 
    name.combined2 as name
    ,city.combined2 as City
    ,sex.combined2 as Sex
    ,country.combined2 as Country
    ,name.common_code
from data as name
join data as city
    on name.common_code = city.common_code
        and city.combined1 = 'City'
join data as sex
    on name.common_code = sex.common_code
        and sex.combined1 = 'Sex'
join data as country
    on name.common_code = country.common_code
        and country.combined1 = 'Country'
where name.combined1 = 'Name'
order by name.common_code;

which gives:

this could be framed in sub-selects (or CTE's) and make NATURAL JOIN do some work also like so:

select distinct
    *
from (
    select combined2 as name, common_code 
    from data 
    where combined1 = 'Name'
) as a
natural join 
(
    select combined2 as city, common_code 
    from data 
    where combined1 = 'City'
) as b
natural join 
(
    select combined2 as sex, common_code 
    from data 
    where combined1 = 'Sex'
) as c  
natural join 
(
    select combined2 as country, common_code 
    from data 
    where combined1 = 'Country'
) as d
;

but now the column order is less under our control (only because I allowed the join order to define it).