Learn how to convert a single row into multiple columns using PIVOT as well as how to covert multiple rows into multiple columns using PIVOT.
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.
We’ll convert row data into column data using custom logic and temp tables, and populate aggregated data in the temp table.
The syntax for pivot clause is as follows:
The parameters or arguments used are as follows:
A PIVOT operator is used to transpose rows into columns. To convert a single row into multiple columns, perform the following.
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:
The PIVOT operator can also be used to convert multiple rows into multiple columns. To convert multiple rows into multiple columns, perform the following:
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:
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:
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!
