TSQL: Concatenate strings from a column into a single row?

The following article is taken from this link:

http://www.aspfaq.com/show.asp?id=2529

This question is asked quite frequently. People want to take a column like this:

Color
------
red
orange
blue
green

And return a resultset like this:

Colors
-------------------------
red,orange,blue,green

This isn't exactly relational, and can certainly be handled by the presentation layer. However, there are kludges in SQL Server that will allow you to derive a solution. We'll start with the simple case above:

CREATE TABLE Colors
(
    Color VARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT Colors SELECT 'red'
INSERT Colors SELECT 'orange'
INSERT Colors SELECT 'blue'
INSERT Colors SELECT 'green'
GO

DECLARE @colors VARCHAR(1024)

SELECT
    @colors = COALESCE(@colors + ',', '') + Color
FROM
    Colors

SELECT Colors = @colors
GO

DROP TABLE Colors
GO

(Note that you can add an ORDER BY clause to the SELECT, but this will not necessarily be obeyed... the resulting list will be displayed in an arbitrary order.)

Now, that's great if you're only concerned with getting one list back. But let's say we have a table of athlete names, events and results. For each athlete, we want a row containing the full name and a comma-separated list of events they medaled in. In this case, we'll use a user-defined function to handle the concatenation for us, and a subquery to limit the output to one row per name.

CREATE TABLE dbo.Medals
(
    FullName VARCHAR(32),
    Result VARCHAR(12),
    Event VARCHAR(32),
    PRIMARY KEY (Fullname, Event)
)
GO

CREATE FUNCTION dbo.GetMedalResult
(
    @fullName VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @r VARCHAR(8000)
    SELECT @r = ISNULL(@r+',', '')
        + Result + ' in ' + Event
        FROM dbo.Medals
        WHERE FullName = @fullName
    RETURN @r
END
GO


SET NOCOUNT ON
INSERT Medals SELECT 'Marie-Helene Premont', 'Silver', 'Mountain Biking'
INSERT Medals SELECT 'Adam van Koeverden', 'Bronze', 'K-1 1,000M Kayak'
INSERT Medals SELECT 'Kyle Shewfelt', 'Gold', 'Gymnastics Floor Exercise'
INSERT Medals SELECT 'Alexandre Despatie', 'Silver', '3-Meter Springboard'
INSERT Medals SELECT 'Michael Phelps', 'Gold', '400M Medley'
INSERT Medals SELECT 'Michael Phelps', 'Gold', '200M Medley'
INSERT Medals SELECT 'Michael Phelps', 'Gold', '200M Butterfly'
INSERT Medals SELECT 'Michael Phelps', 'Gold', '100M Butterfly'
INSERT Medals SELECT 'Michael Phelps', 'Gold', '4x200M Freestyle Relay'
INSERT Medals SELECT 'Michael Phelps', 'Bronze', '200M Freestyle'
INSERT Medals SELECT 'Michael Phelps', 'Bronze', '4x100M Freestyle Relay'
GO

SELECT
    Fullname,
    dbo.getMedalResult(Fullname)
FROM
(
    SELECT
        Fullname
    FROM
        Medals
    GROUP BY
        Fullname
) i
GO

DROP TABLE Medals
DROP FUNCTION dbo.getMedalResult
GO

Again, you will not be able to dictate the ordering of the result in each row; this will be decided by the optimizer at runtime, and will not necessarily stay consistent between executions.

SQL Server 2005 adds some interesting options for this kind of non-relational query. You should be able to have more granular control over concatenation with user-defined aggregates, and you can also take advantage of the new FOR XML PATH (except that for a true comma-delimited list, you have to do a lot of ugly replaces either in the query or in the application/presentation layer):

USE AdventureWorks
GO

SELECT
    CustomerID,
    SalesOrderIDs = REPLACE
    (
        REPLACE(
        REPLACE(
        (
            SELECT
                SalesOrderID AS [data()]
            FROM
                Sales.SalesOrderHeader soh
            WHERE
                soh.CustomerID = c.CustomerID
            ORDER BY
                SalesOrderID
            FOR XML PATH ('o')
        ),
        '</o><o>',','),
        '<o>',SPACE(0)),
        '</o>',SPACE(0)
    )
FROM
    Sales.Customer c
ORDER BY
    CustomerID

Another way is to use the new CROSS APPLY operator:

USE AdventureWorks
GO

SELECT
    CustomerID,
    SalesOrderIDs = LEFT(o.list, LEN(o.list)-1)
FROM
    Sales.Customer c
CROSS APPLY
(
        SELECT
            CONVERT(VARCHAR(12), SalesOrderID) + ',' AS [text()]
        FROM
            Sales.SalesOrderHeader s
        WHERE
            s.CustomerID = c.CustomerID
        ORDER BY
            SalesOrderID
        FOR XML PATH('')
    ) o (list)
ORDER BY
    CustomerID

Comments

[john]

Very good.

[Anon]

Excellent

[R. Schreurs]

Very useful. Avoids the tedious use of cursors.
I never noticed that the variable assignment is evaluated for each row.
Apparently, typical assingments like
SELECT
@colors = Color
FROM
Colors,
in which the query returns multiple rows, are evaluated for each row in turn, overwriting the prior assignment of @colors.

[Dave]

R. Schreurs you are correct. The following post has a lot of useful insight on the bugs that can arise from using SELECT to set variable values.

http://blogs.msdn.com/b/davidlean/archive/2009/08/22/using-tsql-variables-subtle-bug-when-assigning-values-to-variables-via-a-query.aspx

[Buy Viagra]

This blog is really interesting, actually i prefer this kind of information because i can learn too much about it and when somebody else ask me about it, so i will replay correctly. very nice blog!!!!

[Daniel Latikaynen]

@rschreuers: but this one works!?
Is it secure?

DECLARE @@concat VARCHAR(8000)
SELECT @@concat = COALESCE(@@concat + ', ', '') + fieldname from
select @@concat

[Jordan Basketball shoes]

I have always believed that the man who has begun to live more seriously within begins to live more simply without.