Comma Separated List - TSQL

There are times when it is useful to be able to display a list of fields in a comma separated list using just TSQL. In this post I will show my favorite way to make a quick comma separated list. There are a variety of ways to make a list in TSQL, for this example we will make use of the STUFF() function, XML and the AdventureWorks 2008 sample database.

The first step was looking at the ERD to find a one-to-Many relationship that made sense to use a list for. In the ERD we can see that a Person can have multiple phone numbers. So let's use this set of tables for our demonstration.










USE AdventureWorks2008

GO

/* Add multiple phone numbers for a person */

INSERT  INTO person.PersonPhone

        ( BusinessEntityID, PhoneNumber, PhoneNumberTypeID, ModifiedDate )

VALUES  ( 121,'412-555-1212',1, GETDATE()),

        ( 121,'412-432-1212',2, GETDATE()),

        ( 121,'412-278-1919',2, GETDATE())

/* Show the person with phone numbers in a comma separated list */        

SELECT  p.BusinessEntityID

       ,P.FirstName

       ,P.MiddleName

       ,P.LastName

       ,( SELECT    ( STUFF(( SELECT    ', ' + PP.PhoneNumber + ' ' + PT.Name

                              FROM      person.PersonPhone PP

                              JOIN      person.PhoneNumberType PT ON PP.PhoneNumberTypeID = PT.PhoneNumberTypeID

                              WHERE     PP.BusinessEntityID = P.BusinessEntityID

                            FOR

                              XML PATH('') ), 1, 2, '') ) ) AS [Phone Numbers]

FROM    Person.Person P

WHERE   P.BusinessEntityID = 121

This returns the following result: