如何在存储过程中创建动态数据透视表，该存储过程需要报表中当前日期的 12 个月

本文关键字：存储过程 报表 当前日期 个月 存储 过程中 创建 透视 数据 动态 | 更新日期: 2023-09-27 17:56:54

我无法在SQL语句中从当前日期返回12个月以用于我的Stimulsoft报告。sql 表结构是这样的（图 1），请注意，im 是巴西语，因此区域性设置为 pt-BR （日/月/年）

这是 SQL 表

http://img534.imageshack.us/img534/1093/clipboard02xva.jpg

我想这样做，过去 12 个月的当前日期是 01/02/2013，所以我们采取： 

| DACP_Id | FEB 2012 | MAR 2012 | ABR 2012 ... | FEB 2013

。以及追溯到 2013 年 2 月的所有月份。数据透视表中月份内的行是当月DACP_Value。

我是SQL新手，所以我希望你能帮助:)谢谢。

如何在存储过程中创建动态数据透视表，该存储过程需要报表中当前日期的 12 个月

很难从有限的样本数据中判断DACP_Id是否会重复多行和多日期。但是，如果要PIVOT未知数量的日期或可以随时更改的日期，则需要使用动态SQL。

您的代码将类似于以下内容：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(DateName(month, DACP_date) +'_'+cast(Datepart(year, DACP_Date) as varchar(10))) 
                    from yourtable  
                    group by Datepart(month, DACP_date), Datepart(year, DACP_Date), DACP_date
                    order by DACP_date
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query 
    = 'select DACP_id, '+@cols+'
       from
       (
         select DACP_id, 
            DateName(month, DACP_date) +''_''+cast(Datepart(year, DACP_Date) as varchar(10)) date, 
            DACP_Value 
         from yourtable
       ) p
       pivot
       (
          sum(DACP_Value)
          for date in('+@cols+')
       ) piv'
execute(@query)

请参阅带有演示的 SQL 小提琴

从演示中可以看出，您获得月份的多行，这是因为示例数据中的DACP_ID不同。但这就是你在不提前知道价值观的情况下进行转向的方式。

我倾向于不使用动态透视，而是处理应用程序代码中的标头：

WITH Data AS
(   SELECT  DACP_ID,
            DACP_Value,
            [MonthNum] = 12 - DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP)
    FROM    T
    WHERE   DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP) BETWEEN 0 AND 12
)   
SELECT  *
FROM    Data
        PIVOT
        (   SUM(DACP_Value)
            FOR MonthNum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
        ) pvt;

如果您确实需要列标题，请使用：

DECLARE @Col NVARCHAR(MAX) = 
    (   SELECT  ', ' + QUOTENAME(CONVERT(VARCHAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - (12 - Number), 0), 112)) + ' = [' + CAST(number AS VARCHAR) + ']'
        FROM    Master..spt_values
        WHERE   Type = 'P'
        AND     number BETWEEN 0 AND 12
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)');

DECLARE @SQL NVARCHAR(MAX) = 
        N'WITH Data AS
        (   SELECT  DACP_ID,
                    DACP_Value,
                    [MonthNum] = 12 - DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP)
            FROM    T
            WHERE   DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP) BETWEEN 0 AND 12
        )   
        SELECT  DACP_ID' + @Col + '
        FROM    Data
                PIVOT
                (   SUM(DACP_Value)
                    FOR MonthNum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
                ) pvt;';
EXECUTE SP_EXECUTESQL @SQL;

您可以将日期转换为 varchar 时更改值112更改为各种数字，以更改列标题中日期的格式（如问题中所述，103 将为您提供 dd/mm/yyyy）。

SQL Fiddle（无耻地从@bluefeet那里偷来的，谁打败了我）。我在这里留下了这个答案，因为它略有不同，因为它将提供 13 列（去年和本月），无论表中是否存在该月的数据。

相关文章：