Forum Discussion
Pivot after unpivot not working
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?
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)
2 Replies
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)
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!
