Hello everybody:
I have a table with a few columns: 'shipmentid', 'boxid', 'item', 'qty'. Within that table, I have data that looks like this:
I am successfully using this source table to pivot the data to the desired export format that I need so that it looks like this:
Pivot picture on reply post (I'm a new user so I can only post 1 image per post)
The TSQL statement that I am using to get to this point allows for creation of 'n' number of columns depending on how many boxes there are associated with a shipment.
`DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(boxid)
FROM mytable
GROUP BY boxid
order BY boxid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT item,' + @cols + ' from
(
select item, box, qty
from mytable
) x
pivot
(
sum(qty)
for boxid in (' + @cols + ')
) p '
EXECUTE(@query);`
Now to my question...I am wanting to know if anyone can see edits to this PIVOT statement that would allow me to insert a constant value between the column results, so that it looked something like this:
New desired pivot picture on reply post (I'm a new user so I can only post 1 image per post)
Here are the PIVOT results I'm getting currently with my TSQL:
And here is the desired output with the constant values in between the columns for boxid:
You don't need to add anything to the query - add a new text box in the matrix report in SSRS that has your constant value. If that constant value can change depending on the row/column - then you could include that column in the results and set the textbox in the matrix to that field.
Hmmmm. I am not at all familiar with SSRS having never used any of its features. I have a license within my dev environment, so perhaps I'll give it a try.
Thanks for the tip.
Actually, I'm not sure this will work, as my ultimate goal is to export this data. Can you export data directly from SSRS?
You can export reports from SSRS in many formats - Excel, CSV, PDF, Word...
Using SSRS and a matrix would make the process much easier - as you would not have to worry about doing the pivot. You write a query that returns the data in its normal vertical columns and the matrix will pivot it for you.
Exporting data that does not have a defined set of columns will be problematic if there is some other process that needs to read that data into another system. If that is the goal - then you should create the export with the maximum number of pivoted columns...
With that said - downstream processes that need to read this data would handle non-pivoted data much easier. It all depends on the purpose of the export process.
I will not be using this data down stream. I am exporting it from my system to give to another consumer of the data in the format that they need it in.
I'll take a look at SSRS to see what I can come up with. I like the suggestion. I am familiar with MS Access reports and from what I've seen of SSRS on a high level much of the reporting seems to work around the same concepts.
I'll have to figure out how to handle the dynamic number of columns (one shipment might have 2 boxes, but another shipment might have 600 boxes).
I'm trying to work on another way to send this data via XML.