Dynamic month year columns per date range

Question

I need a select statement for a stored procedure with @startDate and @endDate parameters. It needs to return a dynamic amount of columns that are labeled in a month year ("August 2011") format based on the date range parameters.

For example, if @startDate = 1/1/2011 and @endDate = 3/1/2011

the result set would look something like:

column headers ---->  January 2011 | February 2011 | March 2011
rows with data ---->       123     |      3456     |    793

a function taking the datetime corresponding to the column header will be used for rows of data

Is this possible? Use pivot? Thanks in advance for all of your responses!

Answer 1

Assuming you don't need to touch any tables (all data comes from the function). Note that the limit on a local NVARCHAR variable in SQL Server 2000 is 4,000 characters, so you'll need to be careful about how long your range can be.

DECLARE 
    @startDate SMALLDATETIME, 
    @endDate   SMALLDATETIME;

SELECT 
    @startDate = '20110101', 
    @endDate   = '20110301';


DECLARE 
    @i       INT,
    @sd      SMALLDATETIME,
    @sql     NVARCHAR(MAX),
    @column  VARCHAR(32),
    @columns NVARCHAR(4000);

SELECT @i = 0, @columns = N'';

WHILE @i <= DATEDIFF(MONTH, @startDate, @endDate)
BEGIN
    SET @sd = DATEDIFF(DAY, 0, DATEADD(MONTH, @i, @startDate));

    SET @column = DATENAME(MONTH, @sd) 
        + ' ' + CONVERT(VARCHAR(20), YEAR(@sd));

    SET @columns = @columns + ',' + CHAR(13) + CHAR(10) 
        + ' [' + @column + ']' + ' = dbo.FunctionName(''' + @column + ''')';

    SET @i = @i + 1;
END

SET @sql = 'SELECT ' + STUFF(@columns, 1, 1, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

In this case this yields:

SELECT 
 [January 2011] = dbo.FunctionName('January 2011'),
 [February 2011] = dbo.FunctionName('February 2011'),
 [March 2011] = dbo.FunctionName('March 2011');