PIVOT is a relational operator that allows database developers to convert query results from rows to columns. Meanwhile, UNPIVOT will be used to rotate data from columns into rows. In other words, you can change a table-valued expression into another table by using UNPIVOT/ UNPIVOT. And users will designate the column names for the new table. The conditions precedent that enables users to rotate data from columns into rows or vice-versa is data must be aggregated based on aggregation functions (COUNT, SUM, to name a few).
PIVOT and UNPIVOT are essential functionalities that help change how data is displayed in a database so that users can analyze it more easily. Moreover, developers do not need to use complex statements inside a query. Let’s dive in to learn how we can apply the pivot statement and how it works.
How to use the PIVOT operator
- Syntax of PIVOT operator
SELECT <Optional non-pivoted column>,
[Pivoted column 1] AS <column name 1>,
[Pivoted column 2] AS <column name 2>,
...
[Pivoted column n] AS <column name n>
FROM
(<A table, a view, or a Stored procedure, even a Select query>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [Pivoted column 1], [Pivoted column 2],
... [Pivoted column n])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
- Simple example
Let’s create some sample data first:
DECLARE @Candidate TABLE(
Id INT IDENTITY(1,1),
Country NVARCHAR(250),
Score Float)
INSERT INTO @Candidate(Country, Score) VALUES('America', 7)
INSERT INTO @Candidate(Country, Score) VALUES('Canada', NULL)
INSERT INTO @Candidate(Country, Score) VALUES('Vietnam', 9)
INSERT INTO @Candidate(Country, Score) VALUES('Thailand', 9)
INSERT INTO @Candidate(Country, Score) VALUES('Vietnam', 6)
INSERT INTO @Candidate(Country, Score) VALUES('America', 8)
Then apply the PIVOT with a query to count candidates by country.
SELECT [America], [Canada], [Vietnam], [Thailand]
FROM (
SELECT Country FROM @Candidate
) AS src
PIVOT (COUNT(Country) FOR Country IN ([America], [Canada], [Vietnam], [Thailand])) AS pvt
When we look at the following results, we can see that the [Country] column has already been grouped and turned into headers. At the same time, the row data has been aggregated by the COUNT.
Simple example - Pivot query
Let’s look at a more advanced example to see the power of PIVOT.
The use of the GROUP BY statement to group data rows in an aggregated report is insufficient to adequately define the parameters for that report. Given that, in order for the report to be complete and detailed, we can use the PIVOT operator for the statistics and synthesis of data in the most intuitive way.
For example, we can make a report that points out how many candidates in the system come from the same country. And each candidate must be applied for these jobs (‘Developer’, ‘Designer’).
Now we will need two tables that have a relationship with the Candidate table. Yet, to make it clear, these tables will not include FK relationships.
DECLARE @Job TABLE (Id INT, JobName NVARCHAR(255))
INSERT INTO @Job (Id, JobName)
VALUES (1, 'Developer')
INSERT INTO @Job (Id, JobName)
VALUES (2, 'Designer')
INSERT INTO @Job (Id, JobName)
VALUES (3, 'Journalist')
DECLARE @Shortlist TABLE (Id INT, JobId INT, CandidateId INT, STAGE NVARCHAR(255))
INSERT INTO @Shortlist (Id, JobId, CandidateId, STAGE)
VALUES (1, 1, 1, 'Applied')
INSERT INTO @Shortlist (Id, JobId, CandidateId, STAGE)
VALUES (2, 1, 2, 'Applied')
INSERT INTO @Shortlist (Id, JobId, CandidateId, STAGE)
VALUES (3, 2, 3, 'Interview')
INSERT INTO @Shortlist (Id, JobId, CandidateId, STAGE)
VALUES (4, 3, 4, 'Interview')
INSERT INTO @Shortlist (Id, JobId, CandidateId, STAGE)
VALUES (5, 1, 5, 'Hỉred')
INSERT INTO @Shortlist (Id, JobId, CandidateId, STAGE)
VALUES (6, 2, 6, 'Reject')
Once all data is ready, we will follow the syntax to make the query.
Original query:
SELECT c.Id AS CandidateId, c.Country, j.Id AS JobId, j.JobName FROM @Candidate c
JOIN @Shortlist s ON s.CandidateId = c.Id
JOIN @Job j ON s.JobId = j.Id
WHERE j.JobName IN ('Developer', 'Designer')
Pivot query:
SELECT JobName, [America], [Canada], [Vietnam], [Thailand]
FROM (
SELECT c.Id AS CandidateId, c.Country, j.Id AS JobId, j.JobName FROM @Candidate c
JOIN @Shortlist s ON s.CandidateId = c.Id
JOIN @Job j ON s.JobId = j.Id
WHERE j.JobName IN ('Developer', 'Designer')
) AS src
PIVOT (COUNT(CandidateId) FOR Country IN ([America], [Canada], [Vietnam], [Thailand])) AS pvt
Execute the queries, and the result will look like this:
Advanced example - Original Query
Advanced example - Pivot Query
The PIVOT operator will execute a statement similar to GROUP BY for the [Country] data field by aggregated COUNT() function. The grouped [Country] values will be rotated from rows to columns for each value specified in the pivot clause. The value of the [CandidateId] field serves as data columns; while executing, these row data will be grouped via the aggregation function and then allocated for each pivoted column.
Important note: When using aggregate functions with PIVOT, the presence of any null values in the value column is not considered when it comes to computing an aggregation.
How to use the UNPIVOT operator
- Syntax of the UNPIVOT operator
SELECT [Column 1], [Column 2]
FROM (
SELECT [Column 1], [Column 2], <<column_list>> FROM [Pivoted Table]
)
UNPIVOT ( <<value_column>> FOR <<name_column>> IN ( <<column_list>> ) )
<Optional WHERE Clauses>
<Optional ORDER BY Clauses>
- <<value_column>>: Here is the column’s name that merged all value data from the columns in the column list.
- <<name_column>>: This is the column’s name that merged all columns in the column list as value data.
- <<column_list>>: The columns’ names in the source table will be merged into a single pivot column. The column names will extract to name_column, and the column values will populate to <<value_column>>.
UNPIVOT example
UNPIVOT is an operator that can be used to revert the PIVOT result to its original one. However, the UNPIVOT result will not be the same completely as the previous one, we can use a small trick to solve this problem. In some special cases, maybe that will not work.
Assume we have a table that stores the data similar to the result after executing a PIVOT:
UNPIVOT - Original
Now, we will use the UNPIVOT operator to rotate from columns to rows of data. That means the header for ([America], [Canada]) will be reverted to the data row of the Country column. Before that, we need to set up data for this query:
DECLARE @Pivoted Table(Id INT, Score FLOAT, America INT, Canada INT, Vietnam INT, Thailand INT)
INSERT INTO @Pivoted (Id, Score, [America], [Canada], [Vietnam], [Thailand])
SELECT Id, Score, [America], [Canada], [Vietnam], [Thailand]
FROM (
SELECT Id, Country, Id AS [IdToCount], Score FROM @Candidate
) AS src
PIVOT (COUNT([IdToCount]) FOR Country IN ([America], [Canada], [Vietnam], [Thailand])) AS pvt
UNPIVOT query
SELECT Id, Score, Country
FROM (
SELECT Id, Score, America, Canada, Vietnam, Thailand
FROM @Pivoted
) AS pvt
UNPIVOT (Total FOR Country IN (America, Canada, Vietnam, Thailand)) AS unpvt
Here’s the query result:
Unpivot result
The [ID] and [Score] fields will be kept as by the original query. The fields such as [America], [Vietnam], [Canada], and [Thailand] will be defined in the IN operator of the PIVOT, with the [Total] on behalf of aggregated data now becoming a new field data.
Regarding the data that we define, the row data with [total] = 0 are unnecessary. As such, we can exclude those data to get the final result by adding a Where clause to the query above.
... WHERE Total > 0
Here is the final output:
Unpivot final result
Conclusion
Creating a report is an essential part of modern applications that require highly accurate output results as well as query performance (by optimizing the query statements). Besides using GROUP BY, UNION, or CROSS APPLY statements, applying the PIVOT/UNPIVOT relational operator is also a good choice for making a simple query and even improving query performance.
References
- Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs, docs.microsoft.com, 2022.
- UNPIVOT — Snowflake Documentation, docs.snowflake.com.
