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.

Pivoting in SQL Server

Pivoting:

Pivoting is nothing but converting the data available in columns into rows.

In order to pivot the data we need to identify:

a. columns which are not participating in Pivoting

b. Columns which are participating in Pivoting

c. Aggregate operation that needs to be performed

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

CREATE TABLE [dbo].[UnPivotData]

(

[PersonID] [int] NOT NULL,

[PhoneType] [nvarchar](100) NULL,

[PhoneNum] [bigint] NULL

)

Insert the data into the table:

INSERT [dbo].[UnPivotData]

(

[PersonID]

,[PhoneType]

,[PhoneNum]

)

VALUES

(1, 'Home', 1234567890)

,(1, 'Mobile', 8956231470)

,(1, 'Work', 5874693210)

,(1, 'Personal', 9856231470)

,(2, 'Work', 8745236910)

,(2, 'Personal', 6541239870)

,(3, 'Home', 1230546987)

,(3, 'Mobile', 5289631047)

,(4, 'Mobile', 8547236901)

,(4, 'Personal', 8546971203)

,(5, 'Personal', 8546320179)

We have to pivot this data such that, Home, Mobile, Work, Personal should be displayed as columns and PhoneNum should be the value available in those columns.

SELECT

PersonID

,Home

,Mobile

,Work

,Personal --Pivoted columns

FROM

(SELECT

PersonID

,PhoneType

,PhoneNum --Source data

FROM

dbo.UnPivotData

) SRC

PIVOT

(

MAX(PhoneNum) FOR PhoneType IN (Home, Mobile, Work, Personal) --Pivoting

) PIV

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

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

Aggregate operator that we have chosen over here is MAX, because if we have more than one record for those columns which are not participating in Pivoting then SQL Server should know which one it should take from those values.

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

Want to learn Unpivoting go here.