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