SQLUSA
SAVE UP TO 50% ON COMBOS
FREE TRIAL
CLICK HERE TO ORDER

SQL Server 2008 Best Practices
SQL Server 2005 Best Practices

How to concatenate all the last names with xml path?

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

*/

 

The World Leader in SQL Server 2008 Training
The future is just a CLICK away. Your future!
 
SQLUSA.com Home Page

Copyright 2005-2010, SMI Corp. All Rights Reserved.

SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.