Home > SQL Server > SQL Create A Comma Delimited List From a Column

SQL Create A Comma Delimited List From a Column

From SQLServerCentral.com

Create A Comma Delimited List From a Column

Arnie Rowland, March 12, 2008

 

On occasion, there is a desire to concatenate column values from multiple rows to create a comma delimited list.

For example, from the following data:

 

MemberID     Team     FirstName

1     1     Jim

2     1     Mary

3     1     Bob

4     2     Sue

5     2     Ralph

6     2     Ellen

7     3     Bill

8     3     Linda

 

the desired output is a list of Teams and Team members, in the form of:

 

Team     Members

1     Bob, Jim, Mary

2     Ellen, Ralph, Sue

3     Bill, Linda

 

There are several methods to accomplish the desired output. First a solution for SQL Server 2005 / SQL Server 2008 , and then a solution for SQL Server 2000.

 

Create Sample Data for Both Solutions

 

— Suppress data loading messages

SET NOCOUNT ON

 

— Create Sample Data

CREATE TABLE TeamInfo

( MemberID int IDENTITY,

TeamID int,

FirstName varchar(50)

)

 

— Load Sample Data

INSERT INTO TeamInfo VALUES ( 1, ‘Jim’ )

INSERT INTO TeamInfo VALUES ( 1, ‘Mary’ )

INSERT INTO TeamInfo VALUES ( 1, ‘Bob’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Sue’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Ralph’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Ellen’ )

INSERT INTO TeamInfo VALUES ( 3, ‘Bill’ )

INSERT INTO TeamInfo VALUES ( 3, ‘Linda’ )

 

Return to Top

 

 

SQL Server 2005 / SQL Server 2008 Solution

 

–Retrieve desired data

SELECT

t1.TeamID,

MemberList = substring((SELECT ( ‘, ‘ + FirstName )

FROM TeamInfo t2

WHERE t1.TeamID = t2.TeamID

ORDER BY

TeamID,

FirstName

FOR XML PATH( ” )

), 3, 1000 )FROM TeamInfo t1

GROUP BY TeamID

 

— Results

TeamID MemberList

1    Bob, Jim, Mary

2    Ellen, Ralph, Sue

3    Bill, Linda

 

Return to Top

 

 

SQL 2000 Solution

 

— SQL 2000, Retrieve desired data

— With SQL 2000, we will create a User Defined Function to do the concatenation.

— While this solution can also be used with SQL Server 2005/SQL Server 2008,

— the previous suggestion is more efficient.

 

CREATE FUNCTION dbo.fnMakeTeamList

( @TeamID int )

RETURNS varchar(1000)

AS

BEGIN

DECLARE @TempTeam table

( Firstname varchar(20) )

DECLARE @TeamList varchar(1000)

SET @TeamList = ”

INSERT INTO @TempTeam

SELECT FirstName

FROM TeamInfo

WHERE TeamID = @TeamID

IF @@ROWCOUNT > 0

UPDATE @TempTeam

SET @TeamList = ( @TeamList + FirstName + ‘, ‘ )

RETURN substring( @TeamList, 1, ( len( @TeamList ) – 1 ))

END

 

— Usage

SELECT

TeamID,

MemberList = dbo.fnMakeTeamList( TeamId )

FROM TeamInfo

GROUP BY TeamID

 

— Results

TeamID MemberList

1    Jim, Mary, Bob

2    Sue, Ralph, Ellen

3    Bill, Linda

 

Return to Top

 

Clean up the test environment

 

DROP FUNCTION dbo.fnMakeTeamList

DROP TABLE TeamInfo

 

 

Additional Resouces

Pass and Handle a Delimited String similar to handling an Array() of Values.

 

Return to Top

 

___________________________________________________________________________________________________________________

Page Created By: Arnie Rowland, Mar 12, 2008

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: