SQL Query to Convert Rows to Columns in SQL Server



Introduction

The PIVOT operator is used to rotate rows of a table into columns. It is useful for generating cross-tabular reports, where the results are presented in a summary form. The PIVOT operator is available in SQL Server 2005 and later versions.

The PIVOT operator is used to convert rows into columns in a SQL Server database. It is often used to generate cross-tabular reports, where the results are presented in a summary form.

Definition

In SQL Server, the PIVOT operator allows you to convert rows into columns. It is useful for generating cross-tabular reports, where the results are presented in a summary form.

To use the PIVOT operator, you need to specify the following −

  • The source table, which contains the rows that you want to convert into columns.

  • The pivot column, which contains the values that you want to use as the new column names.

  • The aggregated column, which contains the values that you want to use as the column values.

  • The pivot clause, which specifies how the rows should be transformed into columns.

Syntax

Here is the general syntax for using the PIVOT operator −

SELECT *
FROM
(
   SELECT column_list
   FROM table_name
) AS sourceTable
PIVOT
(
   aggregated_function(aggregated_column)
   FOR pivot_column IN (column_list)
) AS pivotTable;

In this syntax −

  • column_list is a list of columns that you want to include in the resulting table.

  • table_name is the name of the source table.

  • aggregated_function is a function such as SUM, AVG, MIN, MAX, etc., that is used to calculate a summary value for the aggregated column.

  • aggregated_column is the column that you want to summarize in the resulting table.

  • pivot_column is the column that contains the values that you want to use as the new column names.

  • column_list is a list of values from the pivot column that you want to use as the new column names.

Example 1

Here is an example of how to use the PIVOT operator −

SQL Query

SELECT * FROM ( SELECT CustomerId, Year, Sales FROM CustomerSales ) AS sourceTable PIVOT ( SUM(Sales) FOR Year IN ([2010], [2011], [2012]) ) AS pivotTable;

In this example, the `CustomerSales` table has three columns: `CustomerId`, `Year`, and `Sales`. The `PIVOT` operator will take the rows of this table and convert them into columns. The resulting table will have a column for each year listed in the `IN` clause (in this case, 2010, 2011, and 2012), and the values in these columns will be the total sales for each customer in each year.

Example 2

Here is an example of how to use the PIVOT operator −

SQL Query

SELECT * FROM ( SELECT EmployeeId, Month, Sales FROM EmployeeSales ) AS sourceTable PIVOT ( AVG(Sales) FOR Month IN ([January], [February], [March]) ) AS pivotTable;

This example will take the rows of the `EmployeeSales` table and convert them into columns. The resulting table will have a column for each month listed in the `IN` clause (in this case, January, February, and March), and the values in these columns will be the average sales for each employee in each month.

Important points of SQL Query to Convert Rows to Columns in SQL Server

Here are some important points to remember when using the PIVOT operator to convert rows to columns in SQL Server −

  • The source table must contain at least two columns: the pivot column and the aggregated column.

  • The pivot column must contain unique values.

  • The aggregated column must be a numerical data type.

  • The pivot clause must specify the values from the pivot column that you want to use as the new column names.

  • The pivot operator must be used in conjunction with an aggregated function, such as SUM, AVG, MIN, or MAX, to calculate a summary value for the aggregated column.

Conclusion

In this article, we learned how to convert Rows to Columns in SQL Server. You can also use the UNPIVOT operator to do the reverse, i.e., to convert columns to rows.


Advertisements