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.