?
Solved

SQL Pivot

Posted on 2012-08-21
3
Medium Priority
?
571 Views
Last Modified: 2012-08-22
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...
0
Comment
Question by:lrbrister
3 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 38317183
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
 
LVL 9

Accepted Solution

by:
keyu earned 2000 total points
ID: 38319296
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
 

Author Closing Comment

by:lrbrister
ID: 38320342
Long...but a viable solution
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question