What is a SQL Pivot Operator

In this section, I will explain about ‘What is a SQL Pivot Operator’ and how to apply it.
The word ‘Pivot’ means to rotate and that is exactly what we do with the SQL Pivot Operator. We rotate rows in to columns.

Example:

In the following example, there is a table on the left side with three different columns, namely Stock Name, Stock Year and Profit. The data inside this table is a little bit confusing and we want a table which shows profit occurring for every stock per year. Meaning, we will have to create a new table having values mentioned under ‘Stock Year’ as separate column headers as shown in the image on the right side.

Syntax of Pivot Operator: SQL Operator can be applied by using the following syntax.

SELECT <non-pivoted column>, [pivoted column] AS <column name>,  ... [pivoted column] AS <column name>
FROM
       (<SELECT query that produces the data>)   AS <alias for the source query>
PIVOT
      (      	  <aggregation function>(column)
                 FOR [<Distinct values of column that will turn into multiple columns>]
                 IN ([pivoted column], [pivoted column],  ... [pivoted column])
      )          AS <alias for the pivot table>  
[ORDER BY clause];

Code: The column with the heading ‘Profit’ is the aggregate column whereas the column with the heading ‘Stock Year’ is the pivot column.

 select stock_name,stock_year, Profit from rawstock

Now, I will show you exactly how to apply Pivot Operator to turn the values mentioned under ‘Stock Year’ in to separate column headers.

select *  from
(
select stock_name,stock_year, Profit from rawstock) as basedata
pivot
(
 
 sum(profit)
  
 for stock_year 
 in ([2008],[2009],[2010])
)
as t

Now, after applying SQL Pivot Operator, I have removed the ‘Stock Year’ column and turned the values mentioned under the ‘Stock Year’ column in to separate column headers. Also, the ‘Profits Column” got removed and the values which were under that column got placed under the newly created column headers accordingly.