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

2021-09-10 00:00:00 sql tsql sql-server

我需要一个带有@startDate 和@endDate 参数的存储过程的select 语句.它需要根据日期范围参数返回以月年(2011 年 8 月")格式标记的动态数量的列.

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 = 1/1/2011 和@endDate = 3/1/2011

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

结果集看起来像:

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!

推荐答案

假设你不需要接触任何表格(所有数据都来自函数).请注意，SQL Server 2000 中本地 NVARCHAR 变量的限制是 4,000 个字符，因此您需要注意范围的长度.

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

相关文章