A SQL Tip: String Concatenate


SQL Server has a lot of useful built-in aggregate functions, but for some reason there is nothing built-in (that I know of,) to concatenate strings. This drives me batty. I have this problem over and over again, and have been looking for an elegant solution for a while now. The problem is always the same:

I have data like this:

Table: Cars

Make Model
Toyota Camry
Ford Focus
Toyota Corolla
Toyota Prius
Jeep Cherokee
Ford Explorer

and I want to get the results, for example, with all the models for Toyota, like this:

Make Models
Toyota Camry, Corolla, Prius

It seems like it should be easy – it seems like it should be as simple as Grouping By the Car’s Make column. I’ve seen solutions where you Create and Drop tables, and even methods where you would create and parse xml from your results. It just seems like there should be an easier way.

Well there is.

Finally I have stumbled upon it, and I wanted to share in case anyone else runs in to this problem as much as I do:

declare @result varchar(50);
select @result = coalesce(@result + ‘, ‘, ”) + [COLUMNNAME]
from [Table]

In our car example above, this becomes

declare @models varchar(50);
select @models = coalesce(@models + ‘, ‘, ”) + MODEL
from CARS
WHERE MAKE = ‘Toyota’;


What do you think?

Connect with Facebook

3 Responses

  1. Nidia Jacinto says:

    Good web site! I genuinely love how it really is pleasant to look at and the details is nicely drafted. So i’m questioning the simplest way I can be warned whenever a fresh article has been made?

  2. Pingback: Combined RSS feeds in Outlook 2010 | bits and bytes