Some time ago I posted about using the UNPIVOT function in a stored procedure here. Recently I came across an interesting problem that required further use of the UNPIVOT command.
Suppose you have a number of columns in a wide table that you would like to unpivot into a single column and multiple rows. An example might be when there are many measure columns and you would like to narrow the table by combining all the measure columns into a single column. This would be a fairly standard use of the UNPIVOT command that was covered in the earlier post.
CREATE TABLE Company(Id INT,CompanyName VARCHAR(500),Contact1 VARCHAR(500),Contact2 VARCHAR(500),Email1 VARCHAR(500),Email2 VARCHAR(500))GOINSERT INTO Company SELECT1, 'FastCarsCo', 'Mr Purple', 'Mr Orange', 'purple@fcc.com', 'orange@fcc.com'UNION ALL SELECT2, 'TastyCakeShop', 'Mr Brown', 'Mr White', 'brown@tcs.com', 'orange@tcs.com'UNION ALL SELECT3, 'KidsToys', 'Mr Pink', 'Mr Black', 'pink@kt.com', 'black@kt.com'UNION ALL SELECT4, 'FruitStall', 'Mr Red', 'Mr Blue', 'red@fs.com', 'blue@fs.com'GOSELECT Id, CompanyName, contact1, contact2, email1, email2FROM CompanyGO
SELECT ID, CompanyName, ContactName,ContactFROM(SELECT ID, CompanyName, Contact1, Contact2FROM Company) srcUNPIVOT(ContactName FOR Contact IN (Contact1, Contact2)) pvt
We have now replaced the contact1 and contact2 fields with a single ContactName field. I have added the new Contact column to indicate the source of the data. This can be removed from the select list if it is not required.
Next we need to perform a similar unpivot on the email columns. This is actually easier that you might think. Rather than nesting queries, or complex CTEs, we need only append our first query with another unpivot statement:
The WHERE clause in the above query is very important. Without it, we haven't told SQL Server how the second set of unpivoted data matches the first. Without the WHERE , it will do a cross join, resulting in Mr Purple occurring twice; once with the correct email address, and once with the address of Mr Orange, his colleague.
In the WHERE clause we state that the new columns we have created, Contact (selected) and Email (not selected) should be joined on the last character. Remember that these two columns are populated with the names of the original fields, ie Contact1, Contact2, and Email1, Email2. The two columns therefore align as Contact1 → Email1 (1=1) and Contact2 → Email2 (2=2). Hence it is important that the names of the original columns in our source Company table contain text such that they can be joined together in the WHERE clause at the end of the query.
More details on the PIVOT / UNPIVOT command can be found here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Next we need to perform a similar unpivot on the email columns. This is actually easier that you might think. Rather than nesting queries, or complex CTEs, we need only append our first query with another unpivot statement:
SELECT Id,CompanyName,ContactName,EmailAddress,ContactFROM(SELECT Id, CompanyName, Contact1, Contact2, Email1, Email2FROM Company) srcUNPIVOT(ContactName FOR Contact IN (Contact1, Contact2 )) pvt1UNPIVOT(EmailAddress For Email IN (Email1, Email2 )) pvt2WHERE RIGHT(Contact,1) = RIGHT(Email,1)
In the WHERE clause we state that the new columns we have created, Contact (selected) and Email (not selected) should be joined on the last character. Remember that these two columns are populated with the names of the original fields, ie Contact1, Contact2, and Email1, Email2. The two columns therefore align as Contact1 → Email1 (1=1) and Contact2 → Email2 (2=2). Hence it is important that the names of the original columns in our source Company table contain text such that they can be joined together in the WHERE clause at the end of the query.
More details on the PIVOT / UNPIVOT command can be found here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Thanks a lot for the post..the where clause really does the trick for un-pivoting multiple columns
ReplyDeleteThanks SQL Banana. It helped me a lot to transpose multiple columns to rows.
ReplyDeleteThank you so much for your help. you saved my life
ReplyDeleteCan Anyone please tell me how to this in python
ReplyDeleteThat's awesome! The WHERE clause is really useful. Thanks.
ReplyDeleteBrilliant! This worked perfectly. However, I didn't quite understand the point of the WHERE statement initially, until I bothered to read your last explanation which cleared things up. Great workaround, thank you.
ReplyDeleteIn this case the measure is 2. In column "Contact" the measure is 2 because there is Contact1 and Contact2.
ReplyDeleteBut what if the measure is very high. Could it work? For example a measure where you have Contact1,Contact2,...,Contact5000
If you are asking about a table that has some 5000 columns that you would like unpivoted into a single column then yes, this can be done. Ignoring for now that 5000 exceeds the max limit, what you ask is possible by including the field names in the IN clause.
DeleteThank you very much!
ReplyDeleteWhat if I don't have values lasting with 1,2..
ReplyDeleteThere needs to be some way of uniquely matching the column names together in the where clause to remove the unwanted results of the crossjoin. It doesn't matter whether the groups of columns start, end or contain a common string, so long as you can use it to filter. If this isn't possible with your column names then I would recommend aliasing the field names in the src subquery with values you can filter on, or using a CTE to rename the fields in the same way.
DeleteThank You! It helped me a lot!!
ReplyDeletewhat would the where clause look like if you have a1, b1, c1, d1, e1 and so how. I tried an AND in the where clause but didnt get the right result
ReplyDeleteAND RIGHT(a1,1) = RIGHT(c1,1)
DeleteAND RIGHT(a1,1) = RIGHT(d1,1)
That should do it, if you have added the extra fields to the src subquery and included the extra unpivot clauses.
Very grateful for this masterclass, Sir.
ReplyDeleteCool!Thank you. It was of great help
ReplyDeleteThis is the coolest thing ever. Thank you very much.
ReplyDeleteThanks so much it's very helpful to us
ReplyDeleteThank you very much
ReplyDeleteThis is what I was looking for yesterday. convert sql server table column to row
I had no idea you could stack UNPIVOT statements! This was incredibly helpful for me, thank you.
ReplyDeletethankyoou so muchhhhhh
ReplyDeleteVery clever solution--thank you!
ReplyDelete