T-SQL: Use A Variable to Create a ColumnList for a PIVOT Query of Dates

DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate = '7/1/2010';
SET @EndDate = '7/31/2010';

--Create a dates table with every date between 7/1/2010 and 7/31/2010
WITH mycte AS
(
SELECT cast(@BeginDate as datetime) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < @EndDate

)
SELECT CONVERT(varchar,DateValue,101) as StDate INTO #dates from mycte
OPTION (MAXRECURSION 0);

--Get each date in the #dates table as a set
select ',[' + StDate + ']' from #dates;

--Get it as a long string. We use the XML path for that, which is awesome.
select ',[' + StDate + ']' from #dates for XML path('');

--Use the STUFF function to remove the leading comma. This is a pretty cool way to do that, rather than messing around with LEN and stuff.
SELECT STUFF((select ',[' + StDate + ']' from #dates for XML path('')),1,1,'') AS NoLeadingCommaOneLongString;

--Use the same query to set a variable value.
DECLARE @ColumnList varchar(max);
SET @ColumnList = STUFF((select ',[' + StDate + ']' from #dates for XML path('')),1,1,'');

--Here's a sample table with dates and names.
CREATE TABLE #SampleWorkDates
(
Workeddate datetime
, [name] varchar(100)
, workdone int
);
INSERT INTO #SampleWorkDates
SELECT '7/12/2010','john', 100 UNION
SELECT '7/12/2010','john', 110 UNION
SELECT '7/12/2010','debby', 102 UNION
SELECT '7/18/2010','john', 60 UNION
SELECT '8/10/2010','alex', 89 UNION
SELECT '9/11/2010','john', 120;

--We need to pivot that data, but the column list is really long.

--Rather than writing out all the columns by hand, like this:
select * from
(select [Name],count([Name]) over (partition by [Name]) as Total,CONVERT(varchar,[WorkedDate],101) as Workeddate,COUNT(*) as cnt from #SampleWorkDates
where CAST(WorkedDate AS datetime) >= '07/01/2010' and CAST(WorkedDate AS datetime) <= '07/31/2010' group by [Name],CONVERT(varchar,[WorkedDate],101))p
pivot (MAX([name]) for WorkedDate in ([07/01/2010],[07/02/2010],[07/03/2010],[07/04/2010],[07/05/2010],[07/06/2010],[07/07/2010],[07/08/2010],[07/09/2010],[07/10/2010],[07/11/2010],[07/12/2010],[07/13/2010],[07/14/2010],[07/15/2010],[07/16/2010],[07/17/2010],[07/18/2010],[07/19/2010],[07/20/2010],[07/21/2010],[07/22/2010],[07/23/2010],[07/24/2010],[07/25/2010],[07/26/2010],[07/27/2010],[07/28/2010],[07/29/2010],[07/30/2010]))pvt


--We can just use the @ColumnList variable we used before. It looks cleaner and is reusable.
declare @sql nvarchar(max);
set @sql = 'select * from (select [Name],count([Name]) over (partition by [Name]) as Total,CONVERT(varchar,[WorkedDate],101) as Workeddate,COUNT(*) as cnt from #SampleWorkDates
where CAST(WorkedDate AS datetime) >= ''' + CONVERT(varchar, @BeginDate, 101) + ''' and CAST(WorkedDate AS datetime) <= ''' + CONVERT(varchar, @EndDate, 101) +
''' group by [Name],CONVERT(varchar,[WorkedDate],101))p
pivot (sum(cnt) for WorkedDate in (' + @ColumnList + '))pvt';

PRINT @sql;

exec(@sql);


DROP TABLE #SampleWorkDates;
DROP TABLE #dates;