Unpivot in SQL Server

UnPivoting:

UnPivoting is nothing but converting rows data into columns.

In order to Unpivot the data we need to identify:

a. columns which are not participating in UnPivoting

b. Columns which are participating in UnPivoting

c. Data to be presented in UnPivoted columns

Let's see how we can do this in SQL:

CREATE TABLE dbo.MobileNum

(

PersonID INT,

Home BIGINT,

Mobile BIGINT,

Work BIGINT,

Personal BIGINT

)

Insert the data into the table:

INSERT INTO dbo.MobileNum

(

PersonID

,Home

,Mobile

,Work

,Personal

)

VALUES

(1, 1234567890, 8956231470, 5874693210, 9856231470)

,(2, NULL, NULL, 8745236910, 6541239870)

,(3, 1230546987, 5289631047, NULL, NULL)

,(4, NULL, 8547236901, NULL, 8546971203)

,(5, NULL, NULL, NULL, 8546320179)

We have to Unpivot this data such that, Home, Mobile, Work, Personal should be displayed as PhoneType column and and values available in those columns should be displayed in PhoneNum column.

SELECT

PersonID

,PhoneType

,PhoneNum --UnPivoted columns

FROM

(SELECT

PersonID

,Home

,Mobile

,Work

,Personal --Source Data

FROM

dbo.MobileNum

) SRC

UNPIVOT

(

PhoneNum For PhoneType IN (Home, Mobile, Work, Personal) --UnPivoting

) AS UNP

If you see the query, first we have selected the required data from the MobileNum table and then on top of that we are writing Unpivoting criteria for PhoneType and PhoneNum.

We can leave any value from the PhoneType if we don't want that PhoneType data in the result set.

In UnPivot clause, we are saying PhoneNUm should be displayed in PhoneType and the value of PhoneTypes we need to consider should be Home, Mobile, Work, Personal

On executing the above query our result set will look like this.

Want to learn Pivoting go here.