Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't be afraid to say "I made a mistake"
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

07
August

How Do I Return All the Values of a Group on a Single Line in SQL Reporting Services 2008?

During a recent SQL Server Reporting Services 2008 project I was asked "How Do I Return All the Values of a Group on a Single Line, separated with a vertical line using SQL?

Solution:


There are a number of ways of achieving this, the simplest method is by creating a query:

Using the Xtreme database, and the Product table


SELECT p1.[Product Type ID] ,

       stuff( (SELECT  ' | '+[Product Name]

               FROM Product p2

               WHERE p2.[Product Type ID]  = p1.[Product Type ID]

               ORDER BY [Product Name]

               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

            ,2,1,'')

       AS Product

      FROM Product p1

      GROUP BY [Product Type ID] 


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us


MAKE A COMMENT

Name *

Email Address *

Comment *