/SQL/Sql server: concatinate column value without trailing or leading comma

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;

Source: stackoverflow.com

Similar codes

1 vote223 views5 answers
What is in operator in sql
SQL
42 votes279 views6 answers
Install beautifulsoup
SQL
0 vote332 views1 answer
Variable 'sql_mode' can't be set to the value of 'no_auto_create_user'
SQL
0 vote238 views1 answer
Mssql last day of month
SQL
3 votes216 views3 answers
Mysql select table names
SQL
7 votes221 views3 answers
Select latest entry in sql table
SQL
1 vote217 views1 answer
Sql server update column based on another table
SQL
2 votes165 views4 answers
Mysql change database character set to utf8mb4
SQL
17 votes273 views6 answers
Sql current date
SQL
2 votes222 views4 answers
2nd highest salary in mysql
SQL