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