In focus

How To Convert Rows To Columns Using Pivot In SQL Server

In this article we will learn how to use pivot in T-SQL to convert rows into columns.

Gagan Sharma Apr 08, 2016

To convert Rows of data into Columns, we need to use Pivot in SQL Server.

The PIVOT function is useful to transform the data from rows into columns. Sometimes in the cases when we need to view the output in a different form for better understanding we use Pivot method.

Syntax :
  1. SELECT <non-pivoted column>,  [first pivoted column] AS <column name>,    
  2. [second pivoted column] AS <column name>,  
  3. ...  
  4. [last pivoted column] AS <column name>  
  5. FROM  
  6. (<SELECT query that produces the data>)  
  7. AS <alias for the source query>  
  8. PIVOT  
  9. (  
  10. <aggregation function>(<column being aggregated>)  
  11. FOR  
  12. [<column that contains the values that will become column headers>]  
  13. IN ( [first pivoted column], [second pivoted column],  
  14. ... [last pivoted column])  
  15. ) AS <alias for the pivot table>  
  16. <optional ORDER BY clause>;  
Reference Pivot Table Syntax
 
Example: Let us suppose that we have a table with structure as given below.

2
 
Query:  Select * from Products PIVOT( sum (qty) for Cust_Name In (Ajay, Deepak, Faizal, Jitin, Kapil, Manish, Pankaj ) ) As PVTTable 

Output: After performing Pivot the output will look like as given below

3

pivot query select sql server t-sql

COMMENT USING