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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

David ToddSenior 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.