SQL Server – Concatenate a column of multiple data rows into one generated column

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. 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, make_description, 
        (SELECT DISTINCT SUBSTRING
           (replace(replace((
             SELECT DISTINCT m1.model 
           FROM car_make AS m1 
           WHERE a.make = m1.make 
           ORDER BY m1.model 
              FOR XML PATH('')), 
                '</model>',', '),
                  '<model>','') ,1,2000) AS Models 
         FROM car_model) AS ModelsConcat 
     FROM car_make a

If you don’t want the XML type result and instead want just a comma delimited result, then:

SELECT make, make_description,
 (SELECT DISTINCT SUBSTRING((SELECT DISTINCT (m1.model + ', ')
   FROM car_make AS m1
   WHERE a.make = m1.make
   ORDER BY (m1.model + ', ') FOR XML PATH('')) ,1,2000) AS Models 
 FROM car_model) AS ModelsConcat 
 FROM car_make a

If you are adding carriage control to your data, you can add the following ‘type’ method to prevent unfriendly XML characters like this:

SELECT make, make_description,
 (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

If you need to qualify the data that builds the delimited string such as not including null, you can instead put the statement in an outer apply instead of a select statement.

SELECT make, make_description, ModelsConcat.Models
  FROM car_make a
  OUTER APPLY (select distinct SUBSTRING((select distinct (m1.model + ', ')
               from car_make As m1
              where (m1.make = a.make) 
           order by (m1.model + ', ') FOR XML PATH('')) ,1,2000) 
                  AS Models from car_model) as ModelsConcat
 Where ModelsConcat.Models is not null

Leave a Reply Cancel reply