Using the concept of Unpivot

Pivoting and Unpivoting are a very handy features in SQL Server. Unpivoting technique in particular is very handy. Consider an example. Say there is an employee table that has ID,Name,Homeph,MobilePh,Workph fields in the table and there are more than 100 million records. Now a new requirement comes in to add the Faxnumber also. What are the options we have? One is using native ALTER TABLE statement and add the new field.

ALTER TABLE Employee
ADD Faxnum VARCHAR(13)

100 million records, its ok to alter the table. What if there are a billion records and a requirement comes in to add another field say ManagerPh? Again we have to alter the table. The best way to handle these kinds of situations is to use UNPIVOT. Look at the pattern. HomePh,MobilePh,Workph,FaxNum... all are of similar type (Phone numbers). The table can be called as PIVOTED (remember its just an example. to pivot the data we need to USE aggregate functions on one of the filed. Its just for understanding).

We can Unpivot. Create a table Employee with Fields ID,PhoneType, Number
Now we can use UNPIVOT or alternate way is to use Union/UnionAll

Using UNPIVOT:

SELECT ID,PhoneType,Number
FROM Employee
UNPIVOT(Number FOR PhoneType IN(HomePh,MobilePh,Workph)) UNP

This will change the fields to records. So, If at all new phone type needs to be added, there is no need to change the schema of the table. We just need to update the query.

Using UNION ALL

SELECT ID, 'HomePh' AS PhoneType, HomePh AS Number
UNION ALL
SELECT ID, 'MobliePh',MobilePh
UNION ALL
SELECT ID, 'WorkPh',WorkPh

The above UNION ALL statement is same as using UNPIVOT. I used UNION ALL here. UNION also can be used. Logically, UNION will do a DISTINCT on select columns. This decreases the performance. This is very true if the table holds huge amounts of data.