Pivot after unpivot not working

[bjackson]

Hi there! I have a source table that looks like this: 

Source Table: 

Contact Name	Address 1 - City	Address 1 - Country	Address 2 - City	Address 2 - Country
John	Antofagasta	Chile	Brisbane	Australia
Jane	Perth	Australia	Santiago	Chile

As you can see, each contact have multiple addresses (Address 1 and Address 2) and every address have more than one attribute (City and Country). All of this expressed as columns. 

I need to transform the previous table so I can get the addresses columns as rows:

Desired Table:

Contact Name	City	Country
John	Antofagasta	Chile
John	Brisbane	Australia
Jane	Perth	Australia
Jane	Santiago	Chile

I tried unpivoting all the addresses columns from my source touble, but from there, I don't know how to get to my desired table:

Step 1 - Unpivot Source Table:

Contact Name	Attribute	Value
John	Address 1 - City	Antofagasta
John	Address 1 - Country	Chile
John	Address 2 - City	Brisbane
John	Address 2 - Country	Australia
Jane	Address 1 - City	Perth
Jane	Address 1 - Country	Australia
Jane	Address 2 - City	Santiago
Jane	Address 2 - Country	Chile

Step 2 - Split, trim and clean "Attribute" column so I get only the attributes of the address:

Contact Name	Attribute	Value
John	City	Antofagasta
John	Country	Chile
John	City	Brisbane
John	Country	Australia
Jane	City	Perth
Jane	Country	Australia
Jane	City	Santiago
Jane	Country	Chile

Step 3 - Pivot back "Attribute" column so I can get "City" and "Country" as columns and all the attributes values as rows.

This last step is the one I can't make it work. 

How can I do it? 

[HotChilli · Accepted solution]

You would need to keep the (1,2 ) details for the last step to work correctly.

It is possible but I would probably advise a simpler approach:

Duplicate the original table.

In one query remove the (2) columns.

In the other remove the (1) columns.

Edit the column names to be the same in both queries.

Append the queries.

--

If it is important to hold on to the 1,2 details then add a column in each query that identifies the AddressNo (hardcode it to 1 or 2 for the appropriate table)

[bjackson]

I splitted the column Attribute by delimiter equals to "-" resulting in two columns: Column "Attribute.1" with values "Address 1" and "Address 2" and column "Attribute.2" with values "City" and "String". 
After doing some string transformation on the "Attribute.1" column to keep just the number (1 and 2) and then renamining the column to "Address #", I was finally able to pivot the column "Attribute.2" and get the desired table. Thanks for your help!