Execute the following SQL Server T-SQL scripts in SSMS Query Editor to concatenate the last names of AdventureWorks employees:

use AdventureWorks;

-- SQL string concatenation - XML string concatenation

select [Concatenated Last Names ]=

      replace(replace((

      select distinct ', '+LastName as [AllLastNames]

from Person.Contact c

inner join HumanResources.Employee e

      on e.ContactID = c.ContactID

      for xml path('')),

      '<AllLastNames>', ''),'</AllLastNames>','')

GO

/* Partial results

Concatenated Last Names

, Abbas, Abercrombie, Abolrous, Ackerman, Adams, Ajenstat, Alberts, Alderson,

Alexander, Altman, Anderson, Ansman-Wolfe, Arifin, Bacon, Baker, Barbariol,

Barber, Barreto de Mattos, Benshoof, Berg, Berge, Berglund, Berndt

*/

-- Eliminate leading comma (,)

-- SQL stuff

select [Concatenated Last Names ]=

      stuff((

      replace(replace((

      select distinct ', '+LastName as [AllLastNames]

      from Person.Contact c

      inner join HumanResources.Employee e

            on e.ContactID = c.ContactID

      for xml path('')),

      '<AllLastNames>', ''),'</AllLastNames>','')),1,1,'')

GO

/* Partial results

Concatenated Last Names

Abbas, Abercrombie, Abolrous, Ackerman, Adams, Ajenstat, Alberts, Alderson,

Alexander, Altman, Anderson, Ansman-Wolfe, Arifin, Bacon, Baker, Barbariol,

Barber, Barreto de Mattos, Benshoof, Berg, Berge, Berglund, Berndt

*/