Monday, 17 November 2014

SQL UNPIVOT on Multiple Columns

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.


Now suppose that there are two sets of measures that you would like kept as separate columns,  Instead of a single MeasureName and MeasureValue column, you would like 2 of each.  Or perhaps a simpler example might not be measure, but dimensional data.  Let's look at CRM system data:

CREATE TABLE Company
(Id INT,
CompanyName VARCHAR(500),
Contact1 VARCHAR(500),
Contact2 VARCHAR(500),
Email1 VARCHAR(500),
Email2 VARCHAR(500)
)
GO
INSERT INTO Company SELECT
1, 'FastCarsCo', 'Mr Purple', 'Mr Orange', 'purple@fcc.com', 'orange@fcc.com'
UNION ALL SELECT
2, 'TastyCakeShop', 'Mr Brown', 'Mr White', 'brown@tcs.com', 'orange@tcs.com'
UNION ALL SELECT
3, 'KidsToys', 'Mr Pink', 'Mr Black', 'pink@kt.com', 'black@kt.com'
UNION ALL SELECT
4, 'FruitStall', 'Mr Red', 'Mr Blue', 'red@fs.com', 'blue@fs.com'
GO

SELECT Id, CompanyName, contact1, contact2, email1, email2
FROM Company
GO



In this example we have a company table with two contacts for each company, and an email address for each contact.  Instead of two columns for contact names and two for email addresses, we would like to reduce this to one of each: CompanyName, ContactName, EmailAddress.  First let's try and reduce the contact1 and contact2 columns to one:
SELECT ID, CompanyName, ContactName,Contact
FROM
(
SELECT ID, CompanyName, Contact1, Contact2
FROM Company
) src
UNPIVOT
(
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:

SELECT Id,
CompanyName,
ContactName,
EmailAddress,
Contact
FROM
(
SELECT Id, CompanyName, Contact1, Contact2, Email1, Email2
FROM Company
) src

UNPIVOT
(
ContactName FOR Contact IN (Contact1, Contact2 )
) pvt1

UNPIVOT
(
EmailAddress For Email IN (Email1, Email2 )
) pvt2

WHERE RIGHT(Contact,1) = RIGHT(Email,1)



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

22 comments:

  1. Thanks a lot for the post..the where clause really does the trick for un-pivoting multiple columns

    ReplyDelete
  2. Thanks SQL Banana. It helped me a lot to transpose multiple columns to rows.

    ReplyDelete
  3. Thank you so much for your help. you saved my life

    ReplyDelete
  4. Can Anyone please tell me how to this in python

    ReplyDelete
  5. That's awesome! The WHERE clause is really useful. Thanks.

    ReplyDelete
  6. Brilliant! 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.

    ReplyDelete
  7. In this case the measure is 2. In column "Contact" the measure is 2 because there is Contact1 and Contact2.
    But what if the measure is very high. Could it work? For example a measure where you have Contact1,Contact2,...,Contact5000

    ReplyDelete
    Replies
    1. 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.

      Delete
  8. Thank you very much!

    ReplyDelete
  9. What if I don't have values lasting with 1,2..

    ReplyDelete
    Replies
    1. There 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.

      Delete
  10. Thank You! It helped me a lot!!

    ReplyDelete
  11. what 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

    ReplyDelete
    Replies
    1. AND RIGHT(a1,1) = RIGHT(c1,1)
      AND 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.

      Delete
  12. Very grateful for this masterclass, Sir.

    ReplyDelete
  13. Cool!Thank you. It was of great help

    ReplyDelete
  14. This is the coolest thing ever. Thank you very much.

    ReplyDelete
  15. Thanks so much it's very helpful to us

    ReplyDelete
  16. Thank you very much
    This is what I was looking for yesterday. convert sql server table column to row

    ReplyDelete
  17. I had no idea you could stack UNPIVOT statements! This was incredibly helpful for me, thank you.

    ReplyDelete
  18. Very clever solution--thank you!

    ReplyDelete