Use of Pivot in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Pivot in SQL Server

When we need to format the result according to user‘s need then we use pivot. Pivot provides us facilities to use the column data as column.

For example:-

Check the following data in table:-

Figure 1

In this table it’s showing the records of no of production of each department in each year. Whenever the department sell particular product, the data is entered in this table according to dept and year.

Now come to pivot table. Suppose I want to see the no of prod sell by each dept in each year. In this case I want to show department name as column.

So I will use pivot as follows:-

select prod_year,[d-101] as Dept1, [d-102] as dept2, [d-103] as dept3, [d-104] as dept4

from (select * from prod_rep) as res

pivot(sum(no_of_prod) for dept in([d-101],[d-102],[d-103],[d-104])) as final_res

the result of this query is as follows:-

Figure 2

As you can see that I used the data of Dept  as column and attached it with prod year and show sum of production for each dept according to this year.