DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Extending Java APIs: Add Missing Features Without the Hassle
  • Comparing Cloud Hosting vs. Self Hosting
  • Observability Architecture: Financial Payments Introduction
  • Effortlessly Streamlining Test-Driven Development and CI Testing for Kafka Developers

Trending

  • Extending Java APIs: Add Missing Features Without the Hassle
  • Comparing Cloud Hosting vs. Self Hosting
  • Observability Architecture: Financial Payments Introduction
  • Effortlessly Streamlining Test-Driven Development and CI Testing for Kafka Developers
  1. DZone
  2. Data Engineering
  3. Databases
  4. Pivoting and Unpivoting Multiple Columns in MS SQL Server

Pivoting and Unpivoting Multiple Columns in MS SQL Server

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

Rathnadevi Manivannan user avatar by
Rathnadevi Manivannan
·
Updated Jul. 09, 17 · Tutorial
Like (5)
Save
Tweet
Share
250.27K Views

Join the DZone community and get the full member experience.

Join For Free

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:select

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:selectThe transposed ratings of the movies are graphically represented using MS Excel as follows:

select

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:

select

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:select

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:select

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

select

And that's it!

sql Microsoft SQL Server Relational database Database

Published at DZone with permission of Rathnadevi Manivannan. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Extending Java APIs: Add Missing Features Without the Hassle
  • Comparing Cloud Hosting vs. Self Hosting
  • Observability Architecture: Financial Payments Introduction
  • Effortlessly Streamlining Test-Driven Development and CI Testing for Kafka Developers

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: