Wednesday, April 1, 2009

UNPIVOT Multiple Columns

Unlike PIVOT which de-normalizes the data, UNPIVOT is more helpful in getting your data back to a more normalized form.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

For a single column UNPIVOT examples and for more on PIVOT and UNPIVOT you can read from Microsoft Books Online - Using PIVOT and UNPIVOT

In this article I’ll try to show you how to UNPIVOT multiple columns in a single query. There is simple trick to do that, if you are familiar with the UNPIVOT operator you will find it easy to understand.

First lets create some sample data :

-- Create sample table
CREATE TABLE Suppliers
(Id INT,
Product VARCHAR(500),
Supplier1 VARCHAR(500),
Supplier2 VARCHAR(500),
Supplier3 VARCHAR(500),
City1 VARCHAR(500),
City2 VARCHAR(500),
City3 VARCHAR(500)
)
GO

-- Load Sample data
INSERT INTO Suppliers SELECT
1, 'Car', 'Tata Motors', 'Bajaj', 'Mahindra', 'Jamshedpur','Pune','Mumbai'
UNION ALL SELECT
2, 'Bike', 'Bajaj', 'Hero Honda', 'Suzuki', 'Pune', 'New Delhi', 'Chandigarh'
UNION ALL SELECT
3, 'Cycle', 'Hercules', 'Hero', 'Atlas', 'Mumbai', 'Banglaore', 'Pune'
GO

-- Test sample data
SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3
FROM Suppliers
GO

Here is how our sample table looks like :

Id Product Supplier1 Supplier2 Supplier3 City1 City2 City3
1 Car TataMotors Bajaj Mahindra Jamshedpur Pune Mumbai
2 Bike Bajaj Hero Honda Suzuki Pune New Delhi Chandigarh
3 Cycle Hercules Hero Atlas Mumbai Bangalore Pune

And as you can see here we have 2 columns which needs to be UNPIVOT, first is Supplier and second is there respective Cities.  If it was single column to be UNPIVOTed it could have been a straight forward task.  Thing is you can write a query with 2 UNPIVOT operators, but question is how to JOIN two UNPIVOT sub queries correctly so we will get the each supplier with its respective city next to it in second column?

As I said earlier there is simple trick to do that.  See the following query and its WHERE condition :

SELECT Id,
    Product,
    ROW_NUMBER()OVER(Partition By Id Order By Suppliers) as SuppId,
    SupplierName,
    CityName
FROM
(
SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3
FROM Suppliers
) Main


UNPIVOT
(
SupplierName FOR Suppliers IN (Supplier1, Supplier2, Supplier3)
) Sup


UNPIVOT
(
CityName For Cities IN (City1, City2, City3)
) Ct


WHERE RIGHT(Suppliers,1) =  RIGHT(Cities,1)


And here is the Output of the query :
Id Product SuppId SupplierName CityName
1 Car 1 Tata Motors Jamshedpur
1 Car 2 Bajaj Pune
1 Car 3 Mahindra Mumbai
2 Bike 1 Bajaj Pune
2 Bike 2 Hero Honda New Delhi
2 Bike 3 Suzuki Chandigarh
3 Cycle 1 Hercules Mumbai
3 Cycle 2 Hero Bangalore
3 Cycle 3 Atlas Pune

So the trick was this where condition -

WHERE RIGHT(Suppliers,1) =  RIGHT(Cities,1)

There are other ways also to UNPIVOT the multiple columns, may be some other time I’ll post them too. 

Source of inspiration for this post is following thread on MSDN forums - http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/54626179-ee9f-4db7-a31e-c02104bdd6beAt 

At the end of it, OP also come up with his own solution he got from SQLTeam’s forums.

Mangal Pardeshi

26 comments:

  1. That works like a charm! You might want to explain clearly that it is the last letter of the columns that makes the magic here:
    Supplier1 corresponds with City1
    Supplier2 corresponds with City2

    etc.

    Thanks anyway!

    ReplyDelete
    Replies
    1. Yes, needed that bit explained to understand what was going on...thank you!

      Delete
  2. Oh Mangal, thank you so much for your post. It saved my day. It took me a while to figureout with my data on "how to do this exactly to return right data"

    And thanks to Stian Sveen for that clue.

    Gr8 job guys!

    ReplyDelete
  3. Please post the details of query[please explain all line by line]

    ReplyDelete
  4. I tried this and nothing returned back. This does not work the way it is shown.

    ReplyDelete
  5. For those who didnt got any records, the trick is Column Names must end with 1,2,3....

    ReplyDelete
  6. For those who didnt got any records, the trick is Column Names must end with 1,2,3....

    ReplyDelete
  7. This is tricky, and heavily dependent on column names. i am looking for solution to similar problem, but independent on column names.

    ReplyDelete
  8. This is tricky and dependent on column names. I am looking for similar solution that is independent on column name

    ReplyDelete
  9. thanks, this examples help me a lot

    ReplyDelete
  10. thanks, this example help me a lot

    ReplyDelete
  11. Used this to get tabular data for a select statement that I used, this was simple and straight to the point.

    ReplyDelete
  12. Yo very slick. Dig it!!!

    ReplyDelete
  13. I worked with this guys.. He is genus..

    ReplyDelete
  14. Thanks.... It helped me a lot

    ReplyDelete
  15. but when you have more than 10 cities and suppliers it doesn't work. how can u fix it when is more than 10? in 11 it breaks

    ReplyDelete
  16. in (WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1))
    from where you have Suppliers and Cities??

    ReplyDelete
  17. Thanks for the great solution, Mangal!

    ReplyDelete
  18. Thank you. This is exactly what I needed.

    ReplyDelete
  19. Thank you. this was really useful.

    ReplyDelete
  20. Thanks for posting this, very helpful.
    If the number of repeating columns go over 9, then the column names would need to be Supplier01, Supplier02, ...
    and the WHERE clause would need to look at the 2 most right characters
    WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1)

    ReplyDelete