sql server: concatinate column value without trailing or leading comma 1

sql server: concatinate column value without trailing or leading comma

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;

Here is what the above code is Doing:
1. Declare a table variable to hold the data.
2. Insert some sample data into the table variable.
3. Use the STUFF function to concatenate the values from the City, Province, and Country columns.
4. Use the COALESCE function to return the first non-null value from the City, Province, and Country columns.
5. Use the RTRIM function to remove trailing spaces from the values returned by the COALESCE function.
6. Use the STUFF function to remove the leading comma and space from the concatenated values.

The output of the above code is:

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

Similar Posts