Pivoting and Unpivoting Multiple Columns in MS SQL Server

Pivoting and Unpivoting Multiple Columns in MS SQL Server

Overview

MS SQL Server, a Relational Database Management System (RDBMS), is used for storing and retrieving data. Data integrity, data consistency, and data anomalies play primary role when storing data into 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, let us discuss about converting values of rows into columns (PIVOT) and values of columns into rows (UNPIVOT) in MS SQL Server.

Pre-requisite

  • Install MS SQL SERVER 2012
  • Create Database MovieLens and table objects based on data modeling and loaded sample data

Use Case

In this use case, let us convert row data into column data using custom logic and temp table, 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 into Reference section.

Data modeling for the sample dataset is as follows:

select

Syntax for Pivot Clause

The syntax for pivot clause is as follows:

Parameters or Arguments

The parameters or arguments used are as follows:

  • first_column – Column or expression displayed as 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 Single Row into Multiple Columns Using Pivot Operator

A PIVOT operator is used to transpose rows into columns.

To convert single row into multiple columns, perform the following:

  • Fetch data from database using the below query:
  • Get aggregated data using Pivot and convert single row into multiple columns using the below query:
The single row transposed into multiple columns is shown in the below diagram:

select

The transposed ratings of the movies are graphically represented using MS Excel as follows:

select

Converting Multiple Rows into Multiple Columns Using Pivot Operator

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:
  • 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 UNPIVOT operator to fetch values from rating, nofuser, and avgr columns and to convert them into one column with multiple rows using the below query:
Multiple columns converted into 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:
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

Conclusion

In this blog, we discussed about pivot operator in MS SQL to transpose data in rows to columns and unpivot operator to transpose data in columns to rows.

References

1866 Views 5 Views Today