连接多个动态数据透视表

Question

我还没有看到这样的问题，但如果有已经得到解答的问题，请告诉我。

我必须使用存储过程创建导出。不幸的是，目前无法在 SSRS 中创建此报告。

我需要做的是动态创建一个数据透视表并将其合并到另一个数据透视表 - 或者这就是我认为可行的方法。

原始数据的工作方式与此类似（我已更改项目以保护我公司的数据）：

他们想要什么报告中的数据如下（为了节省空间，我没有使用所有日期，但您可以理解）：

我创建了一个临时表并创建了两个动态数据透视表。两个表都将单独工作，但是一旦我使用 UNION ALL，我就会收到一条错误消息（我将在下面添加该消息）。我包含了用于创建两个枢轴的代码。有人可以告诉我我做错了什么吗？

是否可以仅通过一个支点来完成此操作？

/*
    Use dynamic SQL to find all 
    Issue Dates for column headings
*/
DECLARE @Jquery VARCHAR(8000)
DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
                        '],[' + 'Item 1' + ' ' + (IssueDate)
                        FROM    #GroupData GroupData
                        ORDER BY '],[' + 'Item 1' + ' ' + (IssueDate)
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @query =
'SELECT * FROM
(
    SELECT LocationID, StoreName, StoreState AS State, "Item 1" + " " + (IssueDate) AS IssueDate, MoneyOrder
    FROM #GroupData GroupData
) MoneyOrderIssued
PIVOT (MAX(MoneyOrder) FOR IssueDate
IN ('+@years+')) AS pvt'

DECLARE @queryMOUsed VARCHAR(4000)
DECLARE @MOUsedYear VARCHAR(2000)
SELECT  @MOUsedYear = STUFF(( SELECT DISTINCT
                        '],[' + 'Item 2' + ' ' + (IssueDate)
                        FROM    #GroupData GroupData
                        ORDER BY '],[' + 'Item 2' + ' ' + (IssueDate)
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @queryMOUsed =
'SELECT * FROM
(
    SELECT LocationID, StoreName, StoreState AS State, "Item 2" + " " + (IssueDate) AS IssueDate, MOUsed
    FROM #GroupData GroupData
)SCRMoneyOrders
PIVOT (MAX(MOUsed) FOR IssueDate
IN ('+@MOUsedYear+')) AS pvt'

SET @Jquery = @query + ' UNION ALL ' +  @queryMOUsed


EXECUTE (@query) -- Only in here to show that this works w/out UNION ALL
EXECUTE (@queryMOUsed) -- Only in here to show that this works w/out UNION ALL

EXECUTE (@Jquery)

我收到的错误消息如下：

警告：聚合或其他 SET 操作消除了空值。 消息 8114，16 级，状态 5，第 1 行 将数据类型 varchar 转换为 bigint 时出错。

Answer 1

我的想法是列不匹配（列数、列顺序和数据类型）。如果我正确读取您的查询，如果 item1 和 item2 的发布日期不匹配，那么无论如何您都可能会得到不匹配的列。如果不看到这两个查询的输出，真的很难判断。

您确定不想要基于商店 ID 的 JOIN 吗？

类似于：

WITH Item1Data as (
--pivot query for item 1
),
Item2Data as (
 --pivot query for item 2
)
SELECT columns
FROM Item1DATA i1
LEFT JOIN Item2Data i2
ON i1.SoteID = i2.StoreID

这是我所做的动态查询。得到的列是数据生成的：

 --Get string of aggregate columns for pivot.  The aggregate columns are the last 5 NRS Years.
                    DECLARE @aggcols NVARCHAR(MAX)

                    SELECT  @aggcols = STUFF(( SELECT   '],['
                                                        + CAST(ny2.NRS_YEAR AS CHAR(4))
                                               FROM     mps.NRS_YEARS ny2
                                               WHERE    ny2.NRS_YEAR BETWEEN @NRS_Year
                                                        - 5 AND @NRS_Year
                                               ORDER BY '],['
                                                        + CAST(ny2.NRS_YEAR AS CHAR(4))
                                             FOR
                                               XML PATH('') ) , 1 , 2 , '')
                            + ']' ;

--While we're at it, get a sum of each year column.  we'll do a union query instead of  rollup because that's how we roll.

                    DECLARE @sumcols NVARCHAR(MAX) ;
                    SELECT  @sumcols = STUFF(( SELECT   ']),sum(['
                                                        + CAST(ny2.NRS_YEAR AS CHAR(4))
                                               FROM     mps.NRS_YEARS ny2
                                               WHERE    ny2.NRS_YEAR BETWEEN @NRS_Year
                                                        - 5 AND @NRS_Year
                                               ORDER BY ']),sum(['
                                                        + CAST(ny2.NRS_YEAR AS CHAR(4))
                                             FOR
                                               XML PATH('') ) , 1 , 3 , '')
                            + '])' ;

                    DECLARE @Query NVARCHAR(MAX) ;
--Construct dynamic pivot query

                    SET @Query = N'SELECT MonthName as Month, ' + @aggcols
                        + N'
      into ##MonthHourPivot
FROM
 (SELECT nc.MONTHNAME, nc.MonthOfNRS_Yr, nc.NRS_YEAR, st.Hours
 FROM mps.NRS_Calendar nc 
 INNER JOIN dbo.StudentTime st
 ON nc.Date = /*00:00:00 AM*/ DATEADD(dd, DATEDIFF(dd, 0, /*On*/ st.EntryDateTime), 0)
 LEFT JOIN mps.vw_ScheduleRoomBuilding srb
 ON st.ScheduleID = srb.ScheduleID
 WHERE (st.EntryDateTime <= GETDATE() and st.SiteCode = ''' + @SiteCode
                        + N''' or ''' + @SiteCode + N''' = ''All'')
 AND (srb.Abbreviation = ''' + @Building + N''' or ''' + @Building
                        + N''' = ''All'')) p
 PIVOT
 (
 sum(p.Hours)
 FOR NRS_Year IN
( ' + @aggcols + N' )
) AS pvt 
' ;

--Execute It.
                    EXECUTE(@Query) ;

                    SET @Query = N'Select [Month], ' + @aggcols
                        + N'FROM ##MonthHourPivot UNION ALL SELECT ''Total'' as [Month], '
                        + @sumcols + ' FROM ##MonthHourPivot' ;
                     Execute (@Query);

Answer 2

这似乎更适合使用 ETL 工具来完成。

我不太了解 Microsoft 工具集，但我认为他们的数据仓库包可以做到这一点。在 Pentaho 的“数据集成”（Kettle）中，您可以使用行反规范化步骤< /a>，或行扁平化步骤。