SQL Pivot

Posted on 2014-08-04
Last Modified: 2016-06-10
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.
Question by:mossmis
    LVL 13

    Accepted Solution

    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

    LVL 14

    Expert Comment

    by:Vikas Garg

    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

    Author Comment

    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?
    LVL 35

    Assisted Solution

    by:David Todd
    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.


    Author Comment

    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.

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now