SQL Pivot

I have a table with the following columns and example data

taskName     Number      runDate
tsk1                 1000         2012-08-21 11:53:10.000
etc...


I want to do a select based on last 2 weeks and return the following considering that today is 8/21/2012

TaskName       8/21    8/20     8/19  etc
tsk1                1000     56        253
tsk2                 0         2000     300
etc...
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
keyuCommented:
you must use pivot option for this...

select taskname,
DATENAME(MM, getdate() ) + '/ ' + CAST(DAY(getdate()) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-1,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-1,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-2,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-2,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-3,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-3,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-4,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-4,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-5,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-5,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-6,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-6,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-7,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-7,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-8,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-8,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-9,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-9,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-10,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-10,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-11,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-11,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-12,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-12,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-13,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-13,getdate())) AS VARCHAR(2)),
DATENAME(MM, dateadd(day,-14,getdate()) ) + '/ ' + CAST(DAY(dateadd(day,-14,getdate())) AS VARCHAR(2))
from (
select TaskName,number,rundate from table_name) p
 pivot (
sum(number)
for rundate in (
getdate(),
dateadd(day,-1,getdate()),
dateadd(day,-2,getdate()),
dateadd(day,-3,getdate()),
dateadd(day,-4,getdate()),
dateadd(day,-5,getdate()),
dateadd(day,-6,getdate()),
dateadd(day,-7,getdate()),
dateadd(day,-8,getdate()),
dateadd(day,-9,getdate()),
dateadd(day,-10,getdate()),
dateadd(day,-11,getdate()),
dateadd(day,-12,getdate()),
dateadd(day,-13,getdate()),
dateadd(day,-14,getdate())
)
) as pvt
0
 
vastoCommented:
You need to convert your date to varchar (Month/Year) and  implement dynamic pivot table as it is shown here:
http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/
0
 
Larry Bristersr. DeveloperAuthor Commented:
Long...but a viable solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.