/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

0 vote287 views1 answer
Vbscript clean up ADODB.Recordset
SQL
0 vote284 views1 answer
Sql server port 1434
SQL
0 vote294 views1 answer
Oracle dynamic select into
SQL
5 votes263 views3 answers
Truncate function in sql oracle
SQL
1 vote317 views3 answers
Reseed sql table primary key
SQL
0 vote293 views1 answer
Open postgresql.conf in centos
SQL
0 vote318 views1 answer
User has exceeded the 'max_questions' resource (current value: 75000)
SQL
0 vote276 views1 answer
Sql get table last modified time
SQL
34 votes295 views6 answers
Install mysql client ubuntu
SQL
7 votes276 views1 answer
Sql management studio display line numbers
SQL