Joining Multiple Dynamic Pivot Tables

Question

I haven't seen a question like this, but if there is one out there that has been answered, please let me know.

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

What I need to do is dynamically create a pivot table and union it to another - or that's what I thought would work.

The raw data works similar to this (I've changed items to protect my company's data):

What they want the data to look like in the report is this (To save space, I didn't use all dates, but you can get the idea):

I've created a temporary table and created two dynamic pivot tables. Both tables will work separately, but once I use a UNION ALL, I receive an error message (I'll add that below). I'm including the code I have used to create the two pivots. Can someone tell me what I'm doing wrong?

Is it possible to do this in just one pivot?

/*
    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 receive is the following:

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

Answer 1

My idea is that the columns do not match (in number of columns, order of columns, and data type). If I'm reading your query correctly, if the issue dates for item1 and item2 do not match, you may get mismatched columns anyway. It is really hard to tell without seeing the output of those two queries.

Are you sure you don't want a JOIN based on store ID?

Something like :

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 a dynamic query I did. got the columns are data-generated:

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

This seems like something more appropriately done with an ETL tool.

I don't know the Microsoft tool-set very well, but I assume their data-warehousing package has something for doing that. In Pentaho's "Data Integration" (Kettle), you'd use a row denormalizer step, or the row flattener step.