Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
25K+ Views
