How to Convert Rows to Columns Using PIVOT in SQL Server

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.

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