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 -- Load Sample data -- Test sample data |
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, |
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