如何从动态数据透视表分配普通表? [英] How can I assign a normal table from a dynamic pivot table?

查看:145
本文介绍了如何从动态数据透视表分配普通表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要从当前日期起过去12个月内在Stimulsoft中进行报告,我使用了动态数据透视表来进行此操作,原始表如图1所示.

I need to make a report in Stimulsoft from the last 12 months from our current date, I used dynamic pivot table to make this, the original table is at figure 1

然后,数据透视表就像图2(此处的大图链接: http://i.stack.imgur.com/LPCuP.jpg )

Then the pivot table is like the figure 2 (bigger image link here: http://i.stack.imgur.com/LPCuP.jpg)

图1中的DACP_Value是它在图2中对应的日期所在的行.请注意,区域性设置为pt-BR(巴西)

The DACP_Value at figure 1 is the row at the date it corresponds at figure 2. Note that the culture is set to pt-BR (brazil)

这是在SQLFiddle中生成的数据透视表的示例代码

Here is a sample code of the generation of the pivot table made in SQLFiddle

http://www.sqlfiddle.com/#!3/3205a/23

我需要将带有标头的动态数据放入普通表(可以是临时表)中,以便可以在报表查询中使用它,并被Stimulsoft软件识别.

I need to put this dynamic data with the headers inside a normal table (it can be a temporary table) so I can use it in my report query and be recognized by the Stimulsoft software.

推荐答案

在代码的SELECT之后添加INTO YourTable:

DECLARE @Col NVARCHAR(MAX) = 
    (   SELECT  ', ' + QUOTENAME(CONVERT(VARCHAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - (12 - Number), 0), 103)) + ' = [' + 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    yourtable
            WHERE   DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP) BETWEEN 0 AND 12
        )   
        SELECT  DACP_ID' + @Col + '
        INTO YourTable --Add this line here
        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

这篇关于如何从动态数据透视表分配普通表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆