Join multiple dynamic pivot tables

Question

I have not seen such a question, but if there is an answer, let me know.

I need to create an export using a stored procedure. Unfortunately, creating this report in SSRS is not currently possible.

I need to dynamically create a pivot table and merge it into another - or what I thought would work.

Raw data works the same way (I changed the elements to protect my company data):

What they want the data to look in the report is (to save space, I did not use all the dates, but you can get this idea):

I created a temporary table and created two dynamic pivot tables. Both tables will work separately, but as soon as I use UNION ALL, I get an error message (I will add this below). I include the code that I used to create the two anchor points. Can someone tell me what I'm doing wrong?

Can this be done with just one stronghold?

/* 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) 

The error message I get is the following:

Warning. Null is excluded by an aggregate or other SET operation. Msg 8114, Level 16, State 5, Line 1 Error converting varchar data type to bigint.

Answer 1

My idea is that the columns do not match (number of columns, column order and data type). If I read your request correctly, if the release dates for item1 and item2 do not match, you can get inconsistent columns anyway. It's really hard to say without seeing the output of these two queries.

Are you sure you do not want a JOIN based on the store ID?

Sort of:

 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 

Here is the dynamic request I made. The resulting columns are generated using data:

  --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 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

This seems like something more appropriate with the ETL tool.

I do not know the Microsoft toolkit very well, but I believe that their data storage package has something to do with it. At Pentaho Data Integration (Kettle), you use the line denormalizer step or the line alignment step .