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.

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

    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.
    Frequently Asked Questions (FAQs)
    What is the PIVOT operator in SQL, and how does it work?

    The PIVOT operator is a powerful SQL tool that allows you to transform rows of data into columns. It’s used to aggregate and present data in a more meaningful way. You can specify which columns become new columns in the result set, making it easier to analyze data based on specific criteria.

     

    What is the syntax of the PIVOT operator?

    The syntax of the PIVOT operator typically includes a SELECT statement with optional non-pivoted columns, followed by the PIVOT keyword. You specify the aggregation function and the column that contains values to become column headers. The result set includes pivoted columns based on the specified criteria.

    Can you provide a simple example of using the PIVOT operator?

    Suppose you have a table of candidates with scores in different countries. You can use the PIVOT operator to count the number of candidates from each country. The PIVOT query will transform the data so that each country becomes a separate column with aggregated counts.

     

    When should I use the PIVOT operator in SQL?

    You should use the PIVOT operator when you need to pivot data for better analysis or reporting. It’s especially useful when you want to transform row-level data into a columnar format based on specific criteria, such as grouping data by categories.

    What is the UNPIVOT operator in SQL, and how does it work?

    The UNPIVOT operator is the reverse of PIVOT. It allows you to rotate data from columns into rows. UNPIVOT is used to transform a table-valued expression into another table by specifying column names. It’s helpful when you need to revert pivoted data back to its original format.

    What is the syntax of the UNPIVOT operator in SQL?

    The syntax of the UNPIVOT operator involves selecting columns from a pivoted table and specifying the columns to be unpivoted. You can choose the value column and the name column, which will store the original column names. The result is a table with rows for each pivoted value.

    Up Next

    July 30, 2021 by Luc Nguyen
    SQL performance tuning can be a challenging practice, particularly when working with large-scale data. As...
    March 17, 2021 by Khoa Nguyen
    Without a doubt, SQL Index is one of the most important factors in the SQL...

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

    Subscribe