Unanswered: Help with pivot\ crosstab query (was "query idea ??")
consider the following table:
name , TaskDate
john , 01/01/2006
john , 01/03/2005
steve , 01/05/2006
i want to build a select statement that gives result like the following:
name , JanuaryTotal , FebruaryTotal
john , 150 , 110
steve , 170 , 50
so the result will be total tasks in specific month, is it doable in one select or should i turn around using hash tables ???
Now - I would google SQL Server crosstabs. The BoL entry for CASE in 2000 was good for this, PIVOT in 2005. I would also think about doing this in your front end (i.e. not in SQL Server). I would also consider whether or not I really want all march 2005's numbers summed with march 2006....
This project is basically a SQLCLR stored procedure called TheMatrix, as long as a SQLCLR UDT called QueryBuilder that allow you to describe all the queries you want to call to build the axis and the content of the matrix you need to retrieve back.
Let me know if you have any issue using this and if you find this useful.
sum(case when month(taskdate)=1 then 1 else 0 end) as January,
sum(case when month(taskdate)=2 then 1 else 0 end) as February,
sum(case when month(taskdate)=3 then 1 else 0 end) as March....
where taskdate >= '1/1/08'
group by name
If you wanted to use max/min then you would change the 1/0 to the actual value.
thnx Krista327 it is the behaviour needed, but am wondering how to make the columns more dynamic, as i did search i found out that i should use functions, but am not familiar with it, and i dont know how to embedded it with the select statement, so am thinking to add a function (eg: GetColumns(1,2,3,...)) that receives the months which i want them to be the columns and then the function returns the that case statements, how this can be done??