Snowflake - How to pivot 2 or multiple columns without aggregation

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).