Tuesday, April 10, 2012

Using Pivot

Some ask a question in asp.net forums.
Question :

 I have an sql server table that looks like this:
The two columns on the table are Salesman and Project.

Salesman------Project
John------------ProjectOne
Mark-----------ProjectTwo
John------------ProjectOne

Output
Salesman------ProjectOne------ProjectTwo-----
John-----------------2------------------0------
Mark----------------0------------------1------

Answer:
Select salesman, IsNull(Project1,0) Project1,IsNull(Project2,0) Project2 from 
(
select salesman, Project ,1 as projectCount from salesproject
) up
pivot (SUM(projectCount) FOR Project in (Project1,Project2)) as pvt
order by salesman

No comments:

Post a Comment