quote:

---

Originally posted by scamquist

I have a table (WeekTaskStatus) with three column: Task, WeekEnd and TaskStatus.
There are currently three tasks (Scope, Budget and Resources) that are updated weekly with the values Green, Yellow and Red.

Task WeekEnd TaskStatus
--------------------------------------------
Scope 1/1/2009 Red
Scope 1/8/2009 Green
Scope 1/15/2009 Green
Budget 1/1/2009 Yellow
Budget 1/8/2009 Red
Budget 1/15/2009 Green
Resources 1/1/2009 Green
Resources 1/8/2009 Red
Resources 1/15/2009 Yellow



The Stored Procedure uses dynamic SQL below creates a pivot table with the Task as the column headers, the WeekEnd as the rows and the TaskStatus as the data.

Scope Budget Resources
--------------------------------------------
1/1/2009 Red Yellow Green
1/8/2009 Green Red Red
1/15/2009 Green Green Yellow
.
.
.


I need to use dynamic SQL because additional tasks may be created and would need to show the additional columns as needed. For example, ModificationReview may be added 1/22/2009.

Is it possible to add a statement in the Stored Procedure to create a table (not temp table), and overwrite when the data changes?



ALTER PROCEDURE dbo.TaskStatusPivot
as

drop table yourdestinationtable
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 +
' into yourdestinationtable from (
select WeekEnd, Task, TaskStatus from WeekTaskStatus) o
pivot (Max(TaskStatus) for Task in (' + @cols + ')) p'

exec(@strSQL)

Any help will be appreciated.

Steve

---