SQL: Concatenate column values in a single row into a string separated by comma

Question

Let's say I have a table like this in SQL Server:

Id    City           Province             Country
1     Vancouver      British Columbia     Canada
2     New York       null                 null
3     null           Adama                null
4     null           null                 France
5     Winnepeg       Manitoba             null
6     null           Quebec               Canada
7     Seattle        null                 USA

How can I get a query result so that the location is a concatenation of the City, Province, and Country separated by ", ", with nulls omitted. I'd like to ensure that there aren't any trailing comma, preceding commas, or empty strings. For example:

Id    Location
1     Vancouver, British Columbia, Canada
2     New York
3     Adama
4     France
5     Winnepeg, Manitoba
6     Quebec, Canada
7     Seattle, USA

Oops ... deleted my wrong answer ... didn't read carefully enough — Smandoli, Jul 05 '12 at 19:23
@AaronBertrand, can you please point me to the duplicates? I spent quite a bit of time looking for an answer on SO, and couldn't find any. — Johnny Oshika, Jul 05 '12 at 19:37
@SQLCurious, I've tried quite a few things, but was surprised that this is a lot more difficult than I would have thought. In a different language, I would simply use something like Array.Join(array, ", "), but I can't find anything remotely similar in SQL. Your comment leads me to believe that you think this is trivial, so can you please provide your answer? — Johnny Oshika, Jul 05 '12 at 19:41
Here's one: http://stackoverflow.com/questions/5235391/how-to-concatenate-columns-properly-using-t-sql — Aaron Bertrand, Jul 05 '12 at 19:42
Sorry - got busy. I thought you might have tried a bunch of case statements. — SQLCurious, Jul 05 '12 at 22:55

Aaron Bertrand · Accepted Answer · 2012-07-05T20:14:00.863

I think this takes care of all of the issues I spotted in other answers. No need to test the length of the output or check if the leading character is a comma, no worry about concatenating non-string types, no significant increase in complexity when other columns (e.g. Postal Code) are inevitably added...

DECLARE @x TABLE(Id INT, City VARCHAR(32), Province VARCHAR(32), Country VARCHAR(32));

INSERT @x(Id, City, Province, Country) VALUES
(1,'Vancouver','British Columbia','Canada'),
(2,'New York' , null             , null   ),
(3, null      ,'Adama'           , null   ),
(4, null      , null             ,'France'),
(5,'Winnepeg' ,'Manitoba'        , null   ),
(6, null      ,'Quebec'          ,'Canada'),
(7,'Seattle'  , null             ,'USA'   );

SELECT Id, Location = STUFF(
      COALESCE(', ' + RTRIM(City),     '') 
    + COALESCE(', ' + RTRIM(Province), '') 
    + COALESCE(', ' + RTRIM(Country),  '')
    , 1, 2, '')
  FROM @x;

SQL Server 2012 added a new T-SQL function called CONCAT, but it is not useful here, since you still have to optionally include commas between discovered values, and there is no facility to do that - it just munges values together with no option for a separator. This avoids having to worry about non-string types, but doesn't allow you to handle nulls vs. non-nulls very elegantly.

Just to say a massive thank you for this....this really helped me out — ikilledbill, Oct 29 '20 at 14:43
Great answer, thanks - one thing I also needed was an empty string check, using `NULLIF`, so: `SELECT Id, Location = STUFF( COALESCE(', ' + RTRIM(NULLIF(City,'')), '') + COALESCE(', ' + RTRIM(NULLIF(Province,'')), '') + COALESCE(', ' + RTRIM(NULLIF(Country,'')), '') , 1, 2, '') FROM @x;` — nathfy, May 25 '21 at 12:09

score 10 · Answer 2 · edited Oct 25 '12 at 14:15

10

select Id ,   
 Coalesce( City + ',' +Province + ',' + Country,
           City+ ',' + Province,
           Province + ',' + Country,
           City+ ',' + Country,
           City,
           Province,
           Country
          ) as location
from table

edited Oct 25 '12 at 14:15

Pranay Rana

175,020
35
237
263

answered Jul 05 '12 at 19:33

Kevin

7,162
11
46
70

Note that CONCAT_NULL_YIELDS_NULL should be set to enabled for this to work. Nice approach to the problem. – Holger Brandt Jul 05 '12 at 19:39
10

I don't think I find this elegant at all. What if there are 5 columns, or 9, or 15? You're really going to list out all of the potential combinations? Yikes. – Aaron Bertrand Jul 05 '12 at 19:44
3

I actually agree with @AaronBertrand My solution will work for this, but it doesn't scale well. The the accepted answer he linked to in his comment under the question works better. – Kevin Jul 05 '12 at 19:59
This will cause problem when there are number of column is more and also here there are around 7 check for three column combination what if there are no of column increase..... – Pranay Rana Jul 05 '12 at 20:00

Gordon Linoff · Answer 3 · 2012-07-05T20:17:41.450

5

This is a hard problem, because the commas have to go in-between:

select id, coalesce(city+', ', '')+coalesce(province+', ', '')+coalesce(country, '')
from t

seems like it should work, but we can get an extraneous comma at the end, such as when country is NULL. So, it needs to be a bit more complicated:

select id,
       (case when right(val, 2) = ', ' then left(val, len(val) - 1)
             else val
        end) as val
from (select id, coalesce(city+', ', '')+coalesce(province+', ', '')+coalesce(country, '') as val
      from t
     ) t

Without a lot of intermediate logic, I think the simplest way is to add a comma to each element, and then remove any extraneous comma at the end.

edited Jul 05 '12 at 20:17

answered Jul 05 '12 at 19:30

Gordon Linoff

1,242,037
58
646
786

This yields for example `New Yor` and `Winnipeg, Manitob`. – Aaron Bertrand Jul 05 '12 at 20:06
Oops, SQL Server ignores the spaces at the end of strings. So the "-2" removed the last character as well. I changed it to "- 1". – Gordon Linoff Jul 05 '12 at 20:18
It's still needlessly complex (at least IMHO). Do you think you need a subquery here or need to check the trailing characters? – Aaron Bertrand Jul 05 '12 at 20:19
To me, this logic is easier to follow than your version, because commas naturally come after words, not before them. Other than that, they are essentially the same (I had actually considered doing it with the commas before, but it looks quite awkward to me). Yours has the advantage that you don't need to know where the comma is to remove it, since it is always at the beginning. – Gordon Linoff Jul 05 '12 at 20:24
I'd argue that introducing a subquery for the sake of placing a comma after a column name than before is unnecessary complexity. YMMV. – Aaron Bertrand Jul 05 '12 at 20:25
We don't actually have to argue. The two versions do essentially the same thing -- construct the answer with extra characters and then remove them. Yours uses nested functions, mine uses subqueries. This is mostly a matter of aesthetics. I do notice, though, that you seem to like to argue. – Gordon Linoff Jul 05 '12 at 20:27
Aaron, I would never downvote something without giving a reason why. I find it rude that about half the time I've been downvoted, no reason has been given. All these downvotes came on answers upvoted by someone else. – Gordon Linoff Jul 05 '12 at 21:12

score 2 · Answer 4 · answered Jul 05 '12 at 19:44

Use the '+' operator.

Understand that null values don't work with the '+' operator (so for example: 'Winnepeg' + null = null), so be sure to use the ISNULL() or COALESCE() functions to replace nulls with an empty string, e.g.: ISNULL('Winnepeg','') + ISNULL(null,'').

Also, if it is even remotely possible that one of your collumns could be interpreted as a number, then be sure to use the CAST() function as well, in order to avoid error returns, e.g.: CAST('Winnepeg' as varchar(100)).

Most of the examples so far neglect one or more pieces of this. Also -- some of the examples use subqueries or do a length check, which you really ought not to do -- just not necessary -- though your optimizer might save you anyway if you do.

Good Luck

+1 This is the only answer other than mine that I can agree with, unfortunately you didn't include any code. :-\ — Aaron Bertrand, Jul 05 '12 at 20:12

score 1 · Answer 5 · answered Jul 05 '12 at 19:39

ugly but it will work for MS SQL:

    select
    id,
    case
        when right(rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,'')),1)=',' then left(rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,'')),LEN(rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,'')))-1)
        else rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,''))
    end
from
    table

This isn't wrong, but it is enormously complex. Also consider how much more complex Kevin's answer will get when there are 4, 5 or 6 columns. — Aaron Bertrand, Jul 05 '12 at 20:09

score 1 · Answer 6 · answered Jan 04 '23 at 09:34

I know it's an old question, but should someone should stumble upon this today, SQL Server 2017 and later has the STRING_AGG function, with the WITHIN GROUP option :

with level1 as
(select id,city as varcharColumn,1 as columnRanking from mytable
union
select id,province,2 from mytable
union
select id,country,3 from mytable)
select STRING_AGG(varcharColumn,', ')
within group(order by columnRanking)
from level1
group by id

Should empty strings exist aside of nulls, they should be excluded with some WHERE clause in level1.

score -2 · Answer 7 · edited Jul 05 '12 at 20:03

-2

Here is an option:

SELECT (CASE WHEN City IS NULL THEN '' ELSE City + ', ' END) + 
(CASE WHEN Province IS NULL THEN '' ELSE Province + ', ' END) +
(CASE WHEN Country IS NULL THEN '' ELSE Country END) AS LOCATION
FROM MYTABLE

edited Jul 05 '12 at 20:03

Aaron Bertrand

272,866
37
466
490

answered Jul 05 '12 at 19:24

mreyeros

4,359
20
24

If City is null, you'll get `, Quebec, Canada`. I don't want the preceding comma. – Johnny Oshika Jul 05 '12 at 19:28
Updated to account for NULL city – mreyeros Jul 05 '12 at 19:30
Still leaves trailing commas, e.g. `Winnipeg, Manitoba,` – Aaron Bertrand Jul 05 '12 at 20:04

SQL: Concatenate column values in a single row into a string separated by comma

7 Answers7

Linked