PIVOT() Function MSSQL Server

Introduction

The PIVOT() function in MS SQL Server is very useful function. This is works like Cross-Tab table in Crystal Report.

Explanation

For an instance, as shown below table, we have customer,Yr,Mothname, and TTL columns. The Year column consisting distinct records 2009,2010 year. If you want to show the Year data as each column header name, then you will have to use the PIVOT() function to show the distinct records as column header.

SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] ,

isnull(sum(total_value),0) TTL

FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date)






Using PIVOT() function

In PIVOT() function, you must specify the column values as shown below,


select * from

(SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] ,

isnull(sum(total_value),0) TTL

FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date) ) s

PIVOT

(

sum(TTL)

FOR yr IN ([2009],[2010])

) p

order by Customer


With our previous query we need to use PIVOT() function and in PIVOT() function have to give the aggregating filed and give the filed/Column name which should show as each column header.

sum(TTL) à This is the Aggregate field

FOR yr IN ([2009],[2010])

Here, give the column name which should show each column header. And give the distinct values of that field as parameter for IN().




After executed the above query you would get this result in which you could notice that 2009,2010 column values became as each column header

In this above query you can give the Month column name instead of Yr column. If try with that you would get the out put as shown below,

Query:


select * from

(SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] ,

isnull(sum(total_value),0) TTL

FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date) ) s

PIVOT

(

sum(TTL)

FOR Month IN ([December],[January])

) p

order by Customer


Output:





Now in the output, Month field distinct values (December and January) are showing as column header mean while Yr column values (2009,2010) are showing as row