Search Wiki:

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
Last edited Apr 28 2008 at 1:54 AM  by ArnieRowland, version 24
Comments
andreabertolotto wrote  Apr 22 2008 at 12:04 PM  
-- Another solution
-- SQL 2005, Retrieve desired data
-- Same solution can also be applied to SQL 2000, changing VARCHAR(MAX) to VARCHAR(8000) or similar
CREATE FUNCTION dbo.fnMakeTeamList
( @TeamID int )
RETURNS varchar(max)
AS
BEGIN
DECLARE @TeamInfo varchar(max)

SELECT @TeamInfo = COALESCE(@TeamInfo + ', ', '') + FirstName
FROM TeamInfo
WHERE TeamID = @TeamID

RETURN @TeamInfo
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

Peso wrote  Apr 24 2008 at 11:42 PM  
Here is another approach to make a nicer looking list

-- Prepare sample data
DECLARE @Sample TABLE (ID INT, Name VARCHAR(9))

INSERT @Sample
SELECT 3, 'Car' UNION ALL
SELECT 1, 'Lion' UNION ALL
SELECT 1, 'Rat' UNION ALL
SELECT 1, 'Mouse' UNION ALL
SELECT 2, 'Apple' UNION ALL
SELECT 2, 'Orange'

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF(
(
SELECT CASE
WHEN Item = 1 AND Items > 1 THEN ' and '
ELSE ', '
END + s2.Name
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY s2.ID ORDER BY s2.Name DESC) AS Item,
COUNT(*) OVER (PARTITION BY s2.ID) AS Items,
s2.Name
FROM @Sample AS s2
WHERE s2.ID = s1.ID
) AS s2
ORDER BY Item DESC
FOR XML PATH('')
), 1, 2, '') AS Items
FROM @Sample AS s1
ORDER BY s1.ID

aboreham wrote  Jun 6 2008 at 9:17 AM  
How can this be done in SQL 2000 without having to create a function?

jtodaa wrote  Jul 17 2008 at 5:03 AM  
Added the following to the TeamInfo table and got some interesting results:

INSERT INTO TeamInfo VALUES ( 3, 'Test: &<>"''' )

Is there any way to prevent xml special characters like >, <, and & from becoming &gt; &lt; and &amp;. Noticed single and double quotes aren't affected.

I've currently got a function to clean this up but would be interested if there's another way:

Create FUNCTION [dbo].[XmlCharReplace]
(
@XMLString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
Declare @CleanedString varchar(max)

Set @CleanedString = Replace(Replace(Replace(@XMLString, '&lt;', '<'), '&gt;', '>'), '&amp;', '&')

Return @CleanedString
END

davedave wrote  Dec 5 2008 at 2:13 AM  
You also need to change &#x0D to char(13)

davedave wrote  Dec 5 2008 at 2:16 AM  
SQL 2008 solution as a function:

create type dttConcatenate as table (ID int identity(1,1), VarcharMax varchar(max) null)
go

create function dfnConcatenate(@tblStrings dttConcatenate readonly, @strDelimiter varchar(max)) returns varchar(max) as begin
declare @strResult varchar(max)

-- change null delimiter to empty string
select @strDelimiter = IsNull(@strDelimiter, '')

-- concatenate the strings in the order they were added to the table
select @strResult = (
select IsNull(t.VarcharMax, '') + @strDelimiter as [text()]
from @tblStrings t
order by t.ID
for XML PATH('')
)

-- undo XML escape sequences
select @strResult = Replace(@strResult, '&amp;', '&')
select @strResult = Replace(@strResult, '&lt;', '<')
select @strResult = Replace(@strResult, '&gt;', '>')
select @strResult = Replace(@strResult, '&#x0D;', char(13))

-- trim off trailing delimiter
if len(@strResult) > 0 and len(@strDelimiter) > 0 select @strResult = Left(@strResult, len(@strResult) - len(@strDelimiter))

return @strResult
end
go


declare @t dttConcatenate, @s varchar(max)
print dbo.dfnConcatenate(@t, '|')
insert into @t values ('a'), ('b'), (''), ('c'), (null), ('d')
select @s = dbo.dfnConcatenate(@t, '|')

print @s
print dbo.dfnConcatenate(@t, null)
insert into @t values ('x' + char(13) + char(10) + 'y'), ('z')
print dbo.dfnConcatenate(@t, char(13) + char(10))
go



drop function dfnconcatenate
drop type dttConcatenate
go




RBarryYoung wrote  Feb 23 2009 at 5:21 PM  
-- ====== =====

Those four REPLACE functions have a lot of overhead. I think that a better solution to the FOR XML Enititization problem is to use the following transformation:

--===== Original FOR XML String Concatenation:
SELECT n + ','
FROM (
SELECT 'a<b' AS n
UNION ALL
SELECT 'b>a'
UNION ALL
SELECT 'b&a'
UNION ALL
SELECT 'b
a') r
FOR XML PATH('')

--Transformed FOR XML String Concatenation:
select (
SELECT n + ','
FROM (
SELECT 'a<b' AS n
UNION ALL
SELECT 'b>a'
UNION ALL
SELECT 'b&a'
UNION ALL
SELECT 'b
a') r
FOR XML PATH(''), TYPE
).value('.[1]','varchar(max)')

-- RBarryYoung
--===== =====

Updating...
Page view tracker