如何从动态数据透视表分配普通表? [英] How can I assign a normal table from a dynamic pivot table?
问题描述
我需要从当前日期起过去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屋!