Create Pivot Table (crosstab) in SQL 2008

I have a table    WeekTaskStatus  with Task WeekEnd and TaskStatus
Task      WeekEnd      TaskStatus
Budget      4/24/2009      Green
Resources      4/24/2009      Yellow
Scope      4/24/2009      Red
Timeline      4/24/2009      Yellow
Budget      5/1/2009      Yellow
Resources      5/1/2009      Green
Scope      5/1/2009      Red
Timeline      5/1/2009      Yellow
Budget      5/8/2009      Green
Resources      5/8/2009      Red
Scope      5/8/2009      Yellow
Timeline      5/8/2009      Green
Budget      5/15/2009      Green
Resources      5/15/2009      Yellow
Scope      5/15/2009      Red
Timeline      5/15/2009      Yellow
Budget      5/22/2009      Yellow
Resources      5/22/2009      Red
Scope      5/22/2009      Red
Timeline      5/22/2009      Yellow

I am trying to create an SQL pivot to display the data as shown below
      Budget      Resources      Scope      Timeline
4/24/2009      Green      Yellow      Red      Yellow
5/1/2009      Yellow      Green      Red      Yellow
5/8/2009      Green      Red      Yellow      Green
5/15/2009      Green      Yellow      Red      Yellow
5/22/2009      Yellow      Red      Red      Yellow

The number of Tasks will increase, so the pivot needs to account for the future additional columns.
There will only be one TaskStatus per WeekEnding per Task.
Is this possible?
LVL 1
ScamquistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
You can look at the PIVOT keyword for SQL keeping in mind that it  requires hard coding of column names (values) to be pivoted.

Here is a nice article by Tim Chapman on the subject:
http://articles.techrepublic.com.com/5100-10878_11-6143761.html

Here is a solution from Mark Wills here on EE:
http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html
0
ScamquistAuthor Commented:
Once the solution is in place, we may add additional tasks.  I would be too cumbersome ot go back and hard code new column names.

I have been looking for awhile and have seen references to having the column created dynamically.  
0
Kevin CrossChief Technology OfficerCommented:
Yes, using dynamic SQL.  Take a look at the second link I posted from Mark talking about that.  His article is providing a nice dynamic SQL procedure which will do this for you OR you can at least learn to build a similar implementation.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ralmadaCommented:
I agree with mwvisa1, dynamic SQL is the way to go here. I have customized the alternative I always use based on the sample table provided. Have a look:
Declare @strSQL varchar(max)
DECLARE @cols varchar(2000)
 
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + task
                        FROM    WeekTaskStatus
                        ORDER BY '],[' + task
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'
 
 
set @strSQL = 'select WeekEnd, ' + @cols +
		' from (
			select WeekEnd, Task, TaskStatus from WeekTaskStatus) o
		  pivot (Max(Task) for Task in (' + @cols + ')) p'
 
exec(@strSQL)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScamquistAuthor Commented:
I think we are almost there,

The code you provided returned the column header in the body.  
I changed line 15 to
pivot (Max(TaskStatus) for Task in (' + @cols + ')) p'

which inserted the Red, Yellow or Green in the body :)  

However, I get and Error Box:
Error - SQL Syntax Errors Encountered
The following errors were encountered while parsing the contenst of the SQL pane.
The Declare SQL construct or statement is not supported.

If I ignore, the query runs, but I can't save it.

Ideas?
0
Kevin CrossChief Technology OfficerCommented:
Are you trying to save this in a view, if so that will not work.  You will have to save it in a stored procedure or table based user defined function.
0
ScamquistAuthor Commented:
Thank you both.  I have been burning up the internet trying to find the answer.  The only answers I found used many dozens of lines of code.  Very complicated.  Thank you so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.