SQLTeam.com | Weblogs | Forums

PIVOT Select Query Help


#1

Hello everybody:

I have a table with a few columns: 'shipmentid', 'boxid', 'item', 'qty'. Within that table, I have data that looks like this:

image

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)


#2

Here are the PIVOT results I'm getting currently with my TSQL:

image


#3

And here is the desired output with the constant values in between the columns for boxid:

image


#4

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.


#5

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.


#6

Actually, I'm not sure this will work, as my ultimate goal is to export this data. Can you export data directly from SSRS?


#7

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.


#8

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.