We could use CONCAT function or + (plus sign) to concatenate multiple columns in SQL Server. Both CONCAT and (+) result if both operands have values different from NULL. When we use CONCAT function the result defaults to a none NULL value while NULL is concatenated with out text whereas when we use (+) the resulting output will default to NULL.
Let us see this example to make the above statement more sense.
SELECT FirstName, MiddleName, FirstName + MiddleName AS [FirstName + MiddleName], CONCAT(FirstName, MiddleName) AS [CONCAT(FirstName, MiddleName)] FROM Person.Person;
SELECT FirstName, Firstname + 123 AS [FirstName + 123] FROM Person.Person;
SELECT FirstName, CONCAT(Firstname , 123) AS [FirstName + 123] FROM Person.Person;
SELECT FirstName, Firstname + GETDATE() AS [FirstName + GETDATE()] FROM Person.Person;
SELECT FirstName, CONCAT(Firstname , GETDATE()) AS [FirstName + GETDATE()] FROM Person.Person;
There is no one function like GROUP_CONCAT() in MySQL that we could use to concat multiple row values into a single column value in SQL Server but we could use other methods like for example if we want to concat all IDs that has has similar FirstName we could do it as
DECLARE @Separator AS NVARCHAR = N'-'; SELECT DISTINCT FirstName, STUFF((SELECT @Separator + CAST(C.BusinessEntityID AS NVARCHAR) FROM person.Person C WHERE P.FirstName = C.FirstName FOR xml path('')), 1, 1, '') AS IDList FROM person.Person P;