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 :
- SELECT <non-pivoted column>, [first pivoted column] AS <column name>,
- [second pivoted column] AS <column name>,
- ...
- [last pivoted column] AS <column name>
- FROM
- (<SELECT query that produces the data>)
- AS <alias for the source query>
- PIVOT
- (
- <aggregation function>(<column being aggregated>)
- FOR
- [<column that contains the values that will become column headers>]
- IN ( [first pivoted column], [second pivoted column],
- ... [last pivoted column])
- ) AS <alias for the pivot table>
- <optional ORDER BY clause>;
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
