SQL Pivot

Experts, I have a SSRS report I'm trying to consolidate lines with (reducing lines by putting multiple serial numbers per row). I've tried to play around with pivots, but no luck. I'm not sure if that is the right way. Please see attached example.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Russell FoxConnect With a Mentor Database DeveloperCommented:
No way that I know of, but if the serial numbers can be together in a single cell as a bunch of comma-separated values, you can use FOR XML PATH to concatenate them together. Here's an example I keep around for when I need to construct something like that - you can just run this to see the output, and then hopefully you can tweak it work in your circumstance. If  you need help with the details I'll need to see your current query:
	CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',3)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'C',2)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'D',1)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'E',0)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'A',9)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'B',10)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',11)
	INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'D',12)

		SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
		FROM #YourTable 
		WHERE (ID = Results.ID) 
		FOR XML PATH (''))
	  ,1,2,'') AS NameValues
	FROM #YourTable Results

	DROP TABLE #YourTable

Open in new window

Vikas GargBusiness Intelligence DeveloperCommented:

For that you have to take matrix in SSRS report and in your query you have to add one row_number field then in the Column group preperties
=Ceiling(Fields!rownumber.Value/5) which will break the matrix into rows with 5 columns

So you can get your desired output
mossmisAuthor Commented:
Vikas...Can you elaborate and possible point me to an example. As of now, I have two queries. One to pull the packing slip lines. And the pulls through serial numbers. The common link is the SKU number. With that said, can the matrix you suggest work or do I need to reformat my query?
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

David ToddConnect With a Mentor Senior DBACommented:

My suggestion is to use this idea to concatenate the serial numbers into a larger varchar column

I can't easily see how to limit this to five. Maybe in your report - if the serial numbers are all the same size - choosing the correct width on a multi-line text box will do what you need.

mossmisAuthor Commented:
I was able to accomplish the basic report using Russell's suggestion. However, the report looks bad as serial numbers can have different lengths and alignment is a nightmare. The data is there, but not aesthetically pleasing to the end user and ultimately customer.

Other than that, it works. Any suggestions to pad the string to kind of fake alignment? I think the max length for a serial number is 12 characters.
mossmisAuthor Commented:
I did a little searching and found the "replicate" function can fluff a field. I used this:

left(mytable.SerialNumber  + ', ' + replicate(' ', 15), 15)

Open in new window

I also chose a fixed width font to help, Courier, to guarantee fixed width and proper alignment.
All Courses

From novice to tech pro — start learning today.