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
