Think with Enlab

Diving deep into the ocean of technology

Stay Connected. No spam!

How to use PIVOT/UNPIVOT statement to make a view report

 

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.

1. Simple example - Pivot query

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:

2. Advanced example - Original Query

Advanced example - Original Query

 

3. Enlab Advanced example - Pivot 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:

4. Enlab UNPIVOT - Original

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:

5. Unpivot 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:

6. Unpivot result

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.

 

CTA Enlab Software


References

About the author

Luc Nguyen

Coding is indispensable spiritual food in my daily life. I’m interested in SQL Server, back-end with C#, and front-end with Angular. I am also a fan of mobile development with Flutter. To relax after work, I often play adventure games and listen to R&B music.

Up Next

How to debug and tune the performance of SQL queries
July 30, 2021 by Luc Nguyen
SQL performance tuning can be a challenging practice, particularly when working with large-scale data. As...
How to use SQL Server Indexes for performance tuning
March 17, 2021 by Khoa Nguyen
Without a doubt, SQL Index is one of the most important factors in the SQL...
Roll to Top

Can we send you our next blog posts? Only the best stuffs.

Subscribe