問題描述

我需要一個帶有@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');

這篇關于每個日期范圍的動態月年列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！