PIVOT UNPIVOT in SQL Server

PIVOT and UNPIVOT

Overview

• Introduced in SQL 2005

• PIVOT transforms row level data to column level

• UNPIVOT transforms column level data to row level data

• Really useful in cross tab functionality

Example of PIVOT

• Let's us take following example.

create table Income(EmpName nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

insert into Income values ('Nisarg', 'FRI', 100)

insert into Income values ('Nisarg', 'MON', 300)

insert into Income values ('Nisarg', 'WED', 500)

insert into Income values ('Nisarg', 'TUE', 200)

insert into Income values ('Swara', 'WED', 900)

insert into Income values ('Nisarg', 'FRI', 100)

insert into Income values ('Swara', 'MON', 300)

insert into Income values ('Swara', 'FRI', 300)

insert into Income values ('Megha', 'TUE', 500)

insert into Income values ('Megha', 'TUE', 200)

insert into Income values ('Nisarg', 'MON', 900)

insert into Income values ('Megha', 'FRI', 900)

insert into Income values ('Megha', 'MON', 500)

insert into Income values ('Nisarg', 'FRI', 300)

insert into Income values ('Nisarg', 'WED', 500)

insert into Income values ('Nisarg', 'FRI', 300)

insert into Income values ('Swara', 'THU', 800)

insert into Income values ('Nisarg', 'TUE', 100)

insert into Income values ('Nisarg', 'THU', 300)

insert into Income values ('Megha', 'WED', 500)

insert into Income values ('Megha', 'THU', 800)

insert into Income values ('Swara', 'TUE', 600)

• Now the table is looking like this

EmpName	IncomeDay	IncomeAmount
Nisarg	FRI	100
Nisarg	MON	300
Nisarg	WED	500
Nisarg	TUE	200
Swara	WED	900
Nisarg	FRI	100
Swara	MON	300
Swara	FRI	300
Megha	TUE	500
Megha	TUE	200
Nisarg	MON	900
Megha	FRI	900
Megha	MON	500
Nisarg	FRI	300
Nisarg	WED	500
Nisarg	FRI	300
Swara	THU	800
Nisarg	TUE	100
Nisarg	THU	300
Megha	WED	500
Megha	THU	800
Swara	TUE	600

• Now, we want result something like this. Basically we want total income of each person for each day as a matrix shown below.

EmpName	MON	TUE	WED	THU	FRI
Megha	500	700	500	800	900
Nisarg	1200	300	1000	300	800
Swara	300	600	900	800	300

• Now, using PIVOT, we can achieve the same thing with very simple query

SELECT        EmpName, [MON],[TUE],[WED],[THU],[FRI]

FROM        Income

PIVOT (

SUM (IncomeAmount)

for IncomeDay in

(

[MON],[TUE],[WED],[THU],[FRI]

)

) as TotalIncomePerDay

• Above query has basically 3 portions
• SELECT
• Contains column names. Basic fields (e.g. EmpName) and Pivoted values (Mon, Tue, etc.)
• FROM
• Which result set you want to PIVOT
• In above example, we have usedIncome table, we can use some query as derived table also
• PIVOT
• Aggregation of a field from base table (mentioned in FROM clause)
• Any aggregation (min, max, avg, sum, etc..) is required, otherwise it will throw an error
• Pivot fields name (e.g. Incomeday)
• Pivot Values ([MON], [TUE], etc..)
• Brackets are not required, but if you would like to have white space in a name of field, then they are required
• Even though a value does not exist in actual table records, still you can mention, it won't throw any error. e.g. Saturday is not part of any resultset, and if I include SAT as a column, it won't throw any error, and simply it will give me NULL in the final resultset

Example of UNPIVOT

• Let's us take following example.
• Assume that we have a table which is having TotalIncome of each employee for each working day as a matrix.

• Following query will create that kind of data for us

SELECT        EmpName, [MON],[TUE],[WED],[THU],[FRI]

INTO        TotalSalary

FROM        Income

PIVOT (

SUM (IncomeAmount)

for IncomeDay in

(

[MON],[TUE],[WED],[THU],FRI

)

) as TotalIncomePerDay

• So TotalSalary is looking something like this

EmpName	MON	TUE	WED	THU	FRI
Megha	500	700	500	800	900
Nisarg	1200	300	1000	300	800
Swara	300	600	900	800	300

• Now we want to transform them into row based data
• Following query will do the same

SELECT        EmpName, IncomeDay, Income

FROM        TotalSalary

UNPIVOT

(

Income

for IncomeDay in

(

[MON],[TUE],[WED],[THU],FRI

)

) as TotalIncomePerDay

• Unlike PIVOT, if you put some wrong value in highlighted portion, it will throw an error. e.g. If we add SAT here, but SAT is not part of columns of Income table, so it will throw an error.

• Once you PIVOT a resultset and then UNPIVOT the same, you won't get actual set of data

Example of Dynamic PIVOT

• Assume that we have rows without static data and we would like to generate cross-tab report dynamically  based on data available at that time.

• Let's assume that, we want to generate cross-tab report where rows will be Days and columns will be EmpName.

• So basically , we are looking for result something like this

IncomeDay	Nisarg	Megha	Swara
FRI	800	900	300
MON	1200	500	300
THU	300	800	800
TUE	300	700	600
WED	1000	500	900

• Now in that case, following query will give the same

SELECT        IncomeDay, [Nisarg], [Megha], [Swara]

FROM        Income

PIVOT (

SUM (IncomeAmount)

for EmpName in

(

[Nisarg], [Megha], [Swara]

)

) as TotalIncomePerDay

• Now, let's say, 2 more employees are getting added to the table, in that case, our result will include 2 more columns, but we don't want to change our query. Currently EmpNames are hard-coded but now we want to pull them from a table and dynamically generate a result set of all available employees

• So in this case, we have to go for dynamic PIVOT

-- Variable Declaration

DECLARE @Query NVARCHAR(MAX)

, @EmpNameXMLString NVARCHAR(MAX)

, @EmpNameString NVARCHAR(MAX)

, @ParmDefinition nvarchar(500);

SELECT @EmpNameXMLString = 'SELECT DISTINCT ''['' + EmpName  + ''],'' FROM Income FOR XML PATH('''')'

SELECT @Query = 'SELECT @EmpNameStringOUT = SUBSTRING((' + @EmpNameXMLString + '), 1,LEN((' + @EmpNameXMLString + '))-1)'

SET @ParmDefinition = N'@EmpNameStringOUT NVARCHAR(MAX) OUTPUT';

-- Building EmpName String

EXECUTE SP_EXECUTESQL @Query, @ParmDefinition, @EmpNameStringOUT=@EmpNameString OUTPUT

--SELECT @EmpNameString

SET @Query =

'

SELECT        IncomeDay, ' + @EmpNameString + '

FROM        Income

PIVOT (

SUM (IncomeAmount)

for EmpName in

(

' + @EmpNameString + '

)

) as TotalIncomePerDay'

EXECUTE SP_EXECUTESQL @Query