Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Excellence can be obtained if you care more than others think is wise, risk more than others think is safe, dream more than others think is practical, expect more than others think is possible
 
Unknown
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 *

To prove you are human what is:

29 + 32 =