dynamic pivot problem

[slatetm]

We are trying to pivot a table to use for an infragistics chart. We have been following this to design the T-SQL:

http://www.theabstractionpoint.com/dynamiccolumns.asp

We are stuck on a problem. The problem is the 2nd to last line. The problem exists in this portion " PIVOT (MAX(CurrentDate) FOR CurrentDate IN ". We narrowed it down to here. Basically what we are trying to do is take a variable number of dates and count how many work items are in progress on each day. We need to flip this and make the dates the column headers.

Any help is appreciated! Thank you.


declare @start datetime
declare @end datetime
Create TABLE LineChartTable(CurrentDate varchar(25), NumofProds int)

Declare firstQuery Cursor for
SELECT StartDate
FROM Product
WHERE (ProjectId = 'abc')
order by StartDate

Declare secondQuery Cursor for
SELECT EndDate
FROM Product
WHERE (ProjectId = 'abc')
order by EndDate desc

Open firstQuery
Fetch Next from firstQuery into @start
close firstQuery
deAllocate firstQuery

Open secondQuery
Fetch Next from secondQuery into @end
close secondQuery
deAllocate secondQuery

WHILE (@start < @end)

BEGIN

INSERT INTO LineChartTable
SELECT CAST(@start as varchar) as CurrentDate, COUNT(*) as NumofProds
from Product
WHERE (ProjectId = 'abc')
and StartDate >= @start and EndDate <= @end

set @start = Dateadd(d, 7, @start)
end


DECLARE @pivotValues varchar(MAX)

SELECT @pivotValues = ''

SELECT @pivotValues = @pivotValues + '''' + Convert(Char(11), CurrentDate, 106) + ''', ' FROM LineChartTable

SELECT @pivotValues = LEFT(@pivotValues, LEN(@pivotValues) - 1)

DECLARE @sqlString nvarchar(MAX)

set @sqlstring = 'select * from LineChartTable PIVOT (MAX(CurrentDate) FOR CurrentDate IN (' + @pivotValues + ')) AS InfoPivot'

EXEC (@sqlString)