Pivoting and Unpivoting Multiple Columns in MS SQL Server

MS SQL Server, a Relational Database Management System (RDBMS), is used for storing and retrieving data. Data integrity, data consistency, and data anomalies play a primary role when storing data into a database. Data is provided in different formats to create different visualizations for analysis. For this purpose, you need to pivot (rows to columns) and unpivot (columns to rows) your data.

A PIVOT relational operator is used to convert values of multiple rows into values of multiple columns. An UNPIVOT relational operator is used to convert values of multiple columns into values of multiple rows.

In this blog, we'll discuss converting values of rows into columns (PIVOT) and values of columns into rows (UNPIVOT) in MS SQL Server.

Prerequisites

• Install MS SQL Server 2012.
• Create a database MovieLens and table objects based on data modeling and loaded sample data.

Use Case

We'll convert row data into column data using custom logic and temp tables, and populate aggregated data in the temp table.

Dataset Description

A sample dataset containing information about movies and its user ratings is used in this use case. For sample dataset, please look to the Reference section. Data modeling for the sample dataset is as follows:

Syntax for Pivot Clause

The syntax for pivot clause is as follows:

SELECT first_column AS <first_column_alias>,
 [pivot_value1], [pivot_value2], ... [pivot_value_n]
 FROM
 (<source_table>) AS <source_table_alias>
 PIVOT
 (
 aggregate_function(<aggregate_column>)
 FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
 ) AS <pivot_table_alias>;

Parameters or Arguments

The parameters or arguments used are as follows:

• first_column: Column or expression displayed as the first column in the pivot table.
• first_column_alias: Column heading for the first column in the pivot table.
• pivot_value1, pivot_value2, ... pivot_value_n: List of values to pivot.
• source_table: SELECT statement providing source data for the pivot table.
• source_table_alias: Alias for source_table.
• aggregate_function: Represents aggregate functions such as SUM, COUNT, MIN, MAX, or  AVG.
• aggregate_column: Column or expression used with the aggregate_function.
• pivot_column: Column containing the pivot values.
• pivot_table_alias: Alias for the pivot table.

Converting a Single Row Into Multiple Columns Using PIVOT

A PIVOT operator is used to transpose rows into columns. To convert a single row into multiple columns, perform the following.

• Fetch data from database using the below query:

/* Getting table data */
WITH cte_result AS( 
 SELECT
 m.movieid ,m.title ,ROUND(r.rating,0) AS [rating],
 CAST(ROUND(r.rating,0) AS VARCHAR(5))+'_rating' AS [Star]
 FROM [movielens].[dbo].[rating] r
 JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid )
SELECT * FROM (
 SELECT
 movieid AS [MovieId],
 title AS [Movie Name],
 CAST(COUNT(*) AS FLOAT) AS [noofuser],
 CAST(SUM(Rating) AS FLOAT) AS [sumofrating],
 CAST(AVG(Rating) AS FLOAT) AS [avgofrating],
 CASE WHEN star IS NULL THEN 't_rating' ELSE star END [RatingGrade]
 FROM cte_result WHERE MovieId <= 2 GROUP BY ROLLUP(movieid,title,star) )ratingfilter
 WHERE [Movie Name] IS NOT NULL ;

• Get aggregated data using PIVOT and convert the single row into multiple columns using the below query:

/* Getting aggregated data using Pivot and converting rows to columns */
WITH cte_result AS(
SELECT 
 m.movieid ,m.title ,ROUND(r.rating,0) AS [rating], 
 CAST(ROUND(r.rating,0) AS VARCHAR(5))+'_rating' AS [Star]
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid )
SELECT 
[MovieId],
[Movie Name],
[1_rating],
[2_rating],
[3_rating],
[4_rating],
[5_rating],
[t_rating] FROM
(SELECT 
 movieid AS [MovieId] ,
 title AS [Movie Name],
 CAST(COUNT(*) AS FLOAT) AS [noofuser],
 CASE WHEN star IS NULL THEN 't_rating' ELSE star END [RatingGrade]
FROM cte_result GROUP BY ROLLUP(movieid,title,star))ratingfilter
PIVOT (SUM([noofuser]) FOR [RatingGrade] IN ([1_rating],[2_rating],[3_rating],[4_rating],[5_rating],[t_rating]))a 
WHERE [Movie Name] IS NOT NULL ORDER BY movieid

The single row transposed into multiple columns is shown in the below diagram:The transposed ratings of the movies are graphically represented using MS Excel as follows:

Converting Multiple Rows Into Multiple Columns Using PIVOT

The PIVOT operator can also be used to convert multiple rows into multiple columns. To convert multiple rows into multiple columns, perform the following:

• Fetch data from database using the below query:

/* Getting table data */
WITH cte_result AS(
SELECT 
 m.movieid,
 m.title,
 ROUND(r.rating,0) AS rating,
 u.gender
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result&nbsp;
GROUP BY movieid,title,gender
ORDER BY movieid,title,gender

• Select rows for conversion into columns as shown in the below diagram:

Multiple rows can be converted into multiple columns by applying both UNPIVOT and PIVOT operators to the result.

Use the UNPIVOT operator to fetch values from the rating, nofuser, and avgr columns and to convert them into one column with multiple rows using the below query:

/* Getting aggregated data using Unpivot and converting column to row */
WITH cte_result AS(
SELECT 
 m.movieid,
 m.title,
 ROUND(r.rating,0) AS rating,
 u.gender
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,gender+'_'+col AS col,value FROM (
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result GROUP BY movieid,title,gender) rt
unpivot ( value FOR col in (rating,nofuser,avgr))unpiv
ORDER BY movieid

Multiple columns converted into a single column are shown in the below diagram:

The PIVOT operator is used on the obtained result to convert this single column into multiple rows.

Get aggregated data using PIVOT and convert multiple rows into multiple columns using the below query:

/* Getting aggregated data using Pivot and converting Multiple rows to Multiple column */
WITH cte_result AS(
SELECT 
 m.movieid,
 m.title,
 ROUND(r.rating,0) AS rating,
 u.gender
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,
[M_nofuser],[F_nofuser],
[M_rating],[F_rating],
[M_avgr],[F_avgr] 
FROM 
(
SELECT movieid,title,gender+'_'+col AS col,value FROM (
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result GROUP BY movieid,title,gender) rt
unpivot ( value FOR col in (rating,nofuser,avgr))unpiv )tp
pivot ( SUM(value) FOR col in ([M_rating],[M_nofuser],[M_avgr],[F_rating],[F_nofuser],[F_avgr])) piv 
ORDER BY movieid

Multiple rows converted into multiple columns are shown in the below diagram:

The transposed movies ratings and its users are graphically represented using MS Excel as follows:

And that's it!