PIVOT and UNPIVOT in Sql Server

PIVOT

There are cases when we need to transpose column values to column names and in such specific cases it makes sense too. 

Sql PIVOT query solves this problem it transposes column values to columns names.

To understand this, below is the test data and expected result set after transposing by PIVOT query.

Test Data For PIVOT Example

Below table shows runs scored by players against different teams.

Pivot_input

 SQL To Generate Test Data


DECLARE  @pvt_input TABLE
(
OppositionTeam VARCHAR(100),
Player VARCHAR(100),
Runs INT
);
INSERT INTO @pvt_input VALUES('Delhi Daredevils', 'Rohit Sharma', 50) -- Runs scored by Rohit Sharma against the team 'Delhi Daredevils'
INSERT INTO @pvt_input VALUES('Delhi Daredevils', 'Rohit Sharma', 20) -- Another record for runs scored by Rohit Sharma against the team 'Delhi Daredevils', this will be aggregated when we apply pivot
INSERT INTO @pvt_input VALUES('Delhi Daredevils', 'Bumrah', 40)
INSERT INTO @pvt_input VALUES('Delhi Daredevils', 'Gopal', 70)
INSERT INTO @pvt_input VALUES('Delhi Daredevils', 'McClenaghan', 100)
INSERT INTO @pvt_input VALUES('Delhi Daredevils', 'Harbhajan', 40)
INSERT INTO @pvt_input VALUES('Rajasthan Royals', 'Rohit Sharma', 80)
INSERT INTO @pvt_input VALUES('Rajasthan Royals', 'Bumrah', 50)
INSERT INTO @pvt_input VALUES('Rajasthan Royals', 'Gopal', 54)
INSERT INTO @pvt_input VALUES('Rajasthan Royals', 'McClenaghan', 51)
INSERT INTO @pvt_input VALUES('Rajasthan Royals', 'Harbhajan', 65)
INSERT INTO @pvt_input VALUES('Gujrat Lions', 'Rohit Sharma', 74)
INSERT INTO @pvt_input VALUES('Gujrat Lions', 'Bumrah', 93)
INSERT INTO @pvt_input VALUES('Gujrat Lions', 'Gopal', 35)
INSERT INTO @pvt_input VALUES('Gujrat Lions', 'McClenaghan', 84)
INSERT INTO @pvt_input VALUES('Gujrat Lions', 'Harbhajan', 64)
INSERT INTO @pvt_input VALUES('Kings XI Panjab', 'Rohit Sharma', 34)
INSERT INTO @pvt_input VALUES('Kings XI Panjab', 'Bumrah', 52)
INSERT INTO @pvt_input VALUES('Kings XI Panjab', 'Gopal', 65)
INSERT INTO @pvt_input VALUES('Kings XI Panjab', 'McClenaghan', 45)
INSERT INTO @pvt_input VALUES('Kings XI Panjab', 'Harbhajan', 54)
INSERT INTO @pvt_input VALUES('Pune Sunrisers', 'Rohit Sharma', 95)
INSERT INTO @pvt_input VALUES('Pune Sunrisers', 'Bumrah', 91)
INSERT INTO @pvt_input VALUES('Pune Sunrisers', 'Gopal', 85)
INSERT INTO @pvt_input VALUES('Pune Sunrisers', 'McClenaghan', 95)
INSERT INTO @pvt_input VALUES('Pune Sunrisers', 'Harbhajan', 65)

SELECT * FROM @pvt_input

Expected Result Set by PIVOT Query

By using PIVOT query, we can transpose above table data to below formate in such a way that players’ names will appear as columns names. Or we can say we want above data in Matrix format. Here column values corresponding to players’ columns show aggregated runs (e.g., sum, avg, max, min. It depends on the aggregate function used in pivot query.)

Pivot_output

 PIVOT Query


-- PIVOT Example Converting Column values (Players) to Column Names
SELECT OppositionTeam,  [Rohit Sharma], [Bumrah], [Gopal], [McClenaghan], [Harbhajan]
FROM
(SELECT OppositionTeam, Player, Runs FROM @pvt_input) AS p
PIVOT
(
-- Here pivot expects an aggregate function like SUM, AVG, MIN, MAX
SUM (Runs)
-- These Column Values will become Column names for Pivot result
FOR Player IN ([Rohit Sharma], [Bumrah], [Gopal], [McClenaghan], [Harbhajan])
) AS pvt
ORDER BY pvt.OppositionTeam;

UNPIVOT

Unlike PIVOT scenario, there are cases where we need to transpose column names to column values.

Sql UNPIVOT query solves this problem it transposes column names to column values.

To understand this, below is the test data and expected result set after transpose by UNPIVOT query.

Test Data For UNPIVOT Example

Unpivot_input

 SQL To Generate Test Data


DECLARE @unpivot_input TABLE
(
OppositionTeam VARCHAR(20),
[Rohit Sharma] INT, Bumrah INT,
Gopal INT,
McClenaghan INT,
Harbhajan INT
)

INSERT INTO @unpivot_input VALUES ('Delhi Daredevils', 70, 40, 70, 100, 40)
INSERT INTO @unpivot_input VALUES ('Gujrat Lions', 74, 93, 35, 84, 64)
INSERT INTO @unpivot_input VALUES ('Kings XI Panjab', 34, 52, 65, 45, 54)
INSERT INTO @unpivot_input VALUES ('Pune Sunrisers', 95, 91, 85, 95, 65)
INSERT INTO @unpivot_input VALUES ('Rajasthan Royals', 80, 50, 54, 51, 65)

SELECT * FROM @unpivot_input

Expected Result by UNPIVOT Query
By using UNPIVOT query, we can transpose above table data to below formate in such a way that column named (players) will appear as column values under column ‘Player’ and runs scored by players will appear under another column ‘Runs’.

Unpivot_output

UNPIVOT QUERY


-- UNPIVOT Example Converting Column Names (Players) to Column values--
SELECT OppositionTeam, Player, Runs
FROM
-- Input data set
(SELECT OppositionTeam, [Rohit Sharma], Bumrah, Gopal, McClenaghan, Harbhajan FROM @unpivot_input) AS up
UNPIVOT
(
-- 'Runs' column holds column values of columns which become column value in Unpivot Result Set
Runs
-- 'Player' column holds column names which become column value in Unpivot Result Set FOR Player IN ([Rohit Sharma], Bumrah, Gopal, McClenaghan, Harbhajan)
) AS unpvt
ORDER By OppositionTeam

Note: PIVOT and UNPIVOT work opposite to each other but in addition to this we need to understand a fact that PIVOT involves aggregation on a column whereas UNPIVOT does not involve any disaggregation.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s