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)
GROUP BY boxid
order BY boxid
FOR XML PATH(''), TYPE
SET @query = 'SELECT item,' + @cols + ' from
select item, box, qty
for boxid in (' + @cols + ')
) p '
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)