Thursday, July 15, 2010

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;

2 comments:

  1. I have read news on technical sites that GiveBIG is now dominating the spring fundraising season for most nonprofits in every sector in Seattle and King County. The nonprofit community and fundraising thought leaders are trying to make sense of the GiveLocalAmerica kerfuffle. Great!!jogos friv gratis 2019
    Jogos 2019
    jogos friv

    ReplyDelete