Saturday, January 1, 2011

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 used Income 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        

No comments:

Post a Comment