SQL Server – Multiple data rows into one generated column, XML Path char(13), and removing that last carriage control in SSRS Expression

Let’s say you have a table called ‘car_make’ and another table called ‘car_model’. There is a one to many relationship between make and model.

You want to bring back a result set of all makes, 1 row per make, but you want only 1 generated column for model that has all the models concatenate into that 1 column.

You also want to add carriage controls so each model will appear on a separate line in your row on your report.

Let’s say you have 4 models for your 1 make of Chevy. Here is the output of what you are striving for:

Make     Model
--------------
Chevy    Astro
         Beretta
         Blazer
         Caprice

The following SQL will give you this result:

SELECT make,
left(ModelsConcat.Models,LEN(ModelsConcat.Models)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',REVERSE(ModelsConcat.Models))+1) As CarModels
OUTER APPLY (SELECT DISTINCT SUBSTRING((SELECT DISTINCT (m1.model + ', ' + + char(13))
 FROM car_make AS m1
 WHERE a.make = m1.make
 ORDER BY (m1.model + ', ' + char(13)) For Xml Path(''), type).value('.', 'nvarchar(max)') ,1,2000) AS Models 
 FROM car_model) AS ModelsConcat 
 FROM car_make a

In your expression, you simply reference the field like this:

=Fields!CarModels.Value