每个日期范围的动态月份列

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.

我需要一个带有@startDate和@endDate参数的存储过程的select语句。它需要返回动态数量的列,这些列基于日期范围参数以月份(“2011年8月”)格式标记。

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

例如,如果@startDate = 1/1/2011和@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!

这可能吗?使用枢轴?在此先感谢您的所有回复!

1 个解决方案

#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.

假设您不需要触摸任何表(所有数据都来自该函数)。请注意,SQL Server 2000中本地NVARCHAR变量的限制为4,000个字符,因此您需要注意您的范围可以有多长。

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');