Monday, December 14, 2015

How to pivot in sql server

Here is the sql query to get pivot data from the database.

Let say we have a database named testDB having the column name DocNo,Customer and Month.
and we need to filter out the Customer in each month from Jan to June.

and we have data like

 001, ABC Customer, 01
 001, ABC Customer, 02
 002, ABC Customer, 03
 003, XYZ Customer, 03


this is the simple sql query to get out the data from the database.

select [01] as Jan,[02] as Feb,[03] as March
from (select DocNo,Customer,Month from testDB)
up pivot (count(DocNo) for [01],[02],[03] in Month) 
as NewPvtTable order by DocNo,Customer,[01],[02],[03]

the above query resulting the newly created pivot table 

No comments:

Post a Comment