Concatenation strings get from columns of table in SQL Server 2012

How join strings comming from columns of table as one result string?

It is several ways for it.
Begin with create database with this example table. And set data into this table with NULL values in some cells.

USE mydb;
GO
SELECT id ,name1 ,name2
FROM mydb.dbo.people ;


First You use add operator between strings get them from diffrent columns.

USE mydb;
GO
SELECT  ( name1 + ' '  + name2 ) as name
FROM mydb.dbo.people ;

You get this message:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Polish_CS_AS" and "Polish_CI_AS" in add operator.

Why? Column name1 has Polish_CI_AS collation, but name2 column has Polish_CS_AS collation. So if You would like to join them You must change one of them for the same as another. Then it will be work. So change collation of name1 column to Polish_CS_AS – collation the same as in name2 column.

ALTER TABLE people
ALTER COLUMN name1 
varchar(100) COLLATE Polish_CS_AS;

Execute it and select statement:

USE mydb;
GO
SELECT  ( name1 + ' '  + name2 ) as name
FROM mydb.dbo.people ;

You see as result this records:
q20

As You may notice if one value  from columns which data are joined is NULL, the result always is NULL ( example: name1=NULL and name2=miller give NULL ) .

How unique this situation?
You may use concat function.
Syntax:

CONCAT(string1, string2, ...  );

In this way You join values from name1 and name2 columns.

USE mydb;
GO
SELECT  CONCAT( name1 , ' ' , name2 ) as name
FROM mydb.dbo.people ;

You see message:
Cannot resolve the collation conflict between "Polish_CS_AS" and "Polish_CI_AI" in the concat operation.

How resolve this problem? The columns are the same collation(Polish_CS_AS), but empty string has Polish_CI_AI collation(inherit from database), so You may change collation it for this string in this query.

USE mydb;
GO
SELECT  CONCAT( name1, ' ' COLLATE Polish_CS_AS , name2)
FROM mydb.dbo.people ;

And it is result:

Notice that if one of column contains NULL value this value is treat as empty string and values from another columns is joined. Last row has NULL values in name1 and name2 columns so You see empty string.
Another way resolve problem with collation of this empty string is change collation database.

USE master;
GO
ALTER DATABASE mydb 
COLLATE Polish_CS_AS;

USE mydb;
GO
SELECT  CONCAT(  name1, ' ' , name2) as name
FROM mydb.dbo.people ;

And it is result: