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: