By JohnnyM


2008-10-11 23:49:59 8 Comments

Consider a database table holding names, with three rows:

Peter
Paul
Mary

Is there an easy way to turn this into a single string of Peter, Paul, Mary?

30 comments

@Chris Shaffer 2008-10-12 00:18:00

This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.

Use COALESCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

Just some explanation (since this answer seems to get relatively regular views):

  • Coalesce is really just a helpful cheat that accomplishes two things:

1) No need to initialize @Names with an empty string value.

2) No need to strip off an extra separator at the end.

  • The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

or:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).

@Graeme Perrow 2009-02-13 12:02:46

To be clear, coalesce has nothing to do with creating the list, it just makes sure that NULL values are not included.

@user166390 2010-08-15 18:57:57

@Graeme Perrow It doesn't exclude NULL values (a WHERE is required for that -- this will lose results if one of the input values is NULL), and it is required in this approach because: NULL + non-NULL -> NULL and non-NULL + NULL -> NULL; also @Name is NULL by default and, in fact, that property is used as an implicit sentinel here to determine if a ', ' should be added or not.

@krubo 2011-06-20 01:14:12

Two ways to fix this to gracefully ignore NULLs: Either SELECT @Names = @Names + ', ' + Name FROM People WHERE Name IS NOT NULL or else SELECT @Names = COALESCE(@Names + ', ' + Name, @Names) FROM People.

@Kirk Broadhurst 2011-08-25 04:34:14

@krubo No, the problem is that @Names = @Names + *anything* will be null because @Names is null upon declaration. The COALESCE resolves both null Name values and the initial null @Names value.

@XpiritO 2011-10-28 14:06:08

This doesn't work for data types varchar and ntext, as they're both incompatible with the add operator.

@Chris Shaffer 2011-10-28 17:11:56

@XpiritO - do you mean text and ntext? Varchar is compatible; text and ntext could be converted (if you are on SQL 2005 convert them to VARCHAR(MAX)/NVARCHAR(MAX) and you don't lose anything; Otherwise you'll have to accept the possibility of truncation anyway, since you can't declare a text/ntext variable).

@fbarber 2012-04-26 02:18:01

Please note that this method of concatenation relies on SQL Server executing the query with a particular plan. I have been caught out using this method (with the addition of an ORDER BY). When it was dealing with a small number of rows it worked fine but with more data SQL Server chose a different plan which resulted in selecting the first item with no concatenation whatsoever. See this article by Anith Sen.

@R. Schreurs 2013-08-02 08:10:54

This method cannot be used as a sub query in a select list or where-clause, because it use a tSQL variable. In such cases you could use the methods offered by @Ritesh

@Shinigamae 2013-10-28 20:09:14

This solution will not work in a view but Ritesh's one would.

@Iain Samuel McLean Elder 2013-12-09 00:33:13

This is the simplest way to build dynamic SQL if you want to apply the same command to many objects. Cade Roux uses it to rename schemas, and I use it to rename tSQLt test classes. Thanks, Chris!

@James L. 2014-05-23 05:16:33

Tried this, loved it at first because of how simple and brilliant it is. But like any other repetitive concat process to a varchar(), I/O and CPU eventually peg. For 20,000 GUIDs, it took 2 minutes to concatenate them, whereas using the for xml path('') took less than 1 second.

@confusedMind 2014-08-08 13:34:43

How to add Distinct to it if i only want to add up distinct values ?

@Shay 2014-08-12 00:16:13

@confusedMind - like this: DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM ( SELECT DISTINCT Name FROM People )

@Marc Durdin 2015-07-15 00:23:20

This is not a reliable method of concatenation. It is unsupported and should not be used (per Microsoft, e.g. support.microsoft.com/en-us/kb/287515, connect.microsoft.com/SQLServer/Feedback/Details/704389). It can change without warning. Use the XML PATH technique discussed in stackoverflow.com/questions/5031204/… I wrote more here: marc.durdin.net/2015/07/…

@Nezam 2015-08-10 18:14:19

A Major problem with this approach is that it would truncate all values which are over 8000.

@binki 2016-06-25 15:43:42

Your explanation doesn't really explain what this does. It's relying on SQL Server executing the expression for every row. That'd be nifty if it can be relied on. But see other comments indicating that SQL Server is not required to do so for this type of query.

@Leonardo Spina 2016-08-30 11:21:51

I found the same exact code example here (posted 6 months before): codeproject.com/Tips/334400/… I think a reference to the author should be due.

@Jon49 2016-12-21 20:40:19

Darn. I upvoted this answer but now that I know that it isn't the correct way to do concatenation because it can screw you over since it is an unsupported feature I can't remove my upvote anymore :-( Now I have misled many.

@BI Dude 2017-05-10 15:32:31

The solution works fine; however, it truncates the text at lenght 65576

@MC9000 2019-02-07 01:19:17

Can't use ORDER BY on this, not to mention this is horrendous advice for reasons others have posted!

@Kemal AL GAZZAH 2019-04-23 17:42:17

We can use RECUSRSIVITY, WITH CTE, union ALL as follows

declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')

declare @myresult as table(id int,str nvarchar(max),ind int, R# int)

;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte

select top 1 str from @myresult order by R# desc

@Ritesh 2009-02-13 11:53:52

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

@Menefee 2012-03-21 18:21:35

I recieve an error "Incorrect syntax near the keyword 'For'" running MS SQL Server 2008 R2

@user140628 2013-04-17 12:35:18

Great solution. The following may be helpful if you need to handle special characters like those in HTML: Rob Farley: Handling special characters with FOR XML PATH('').

@JsonStatham 2013-07-26 14:37:34

What if there is no 'Subject ID'

@Sam 2013-08-13 01:26:59

Apparently this doesn't work if the names contain XML characters such as < or &. See @BenHinman's comment.

@James L. 2014-05-23 05:19:00

This is a good solution. I concatenated 20,000 GUIDs with it in less than 1 second. for xml path('') performs far better than any type cursor and/or variable concat approach.

@Bacon Bits 2014-11-13 18:54:02

NB: This method is reliant on undocumented behavior of FOR XML PATH (''). That means it should not be considered reliable as any patch or update could alter how this functions. It's basically relying on a deprecated feature.

@Whelkaholism 2015-03-23 10:54:57

@Bacon Bits - Could you explain that a bit more? It's an incredibly widely used piece of code.

@Bacon Bits 2015-03-23 14:00:33

@Whelkaholism That was Microsoft's initial response to this query. I can't seem to find their initial statement anymore (as you said, it's very widely used), but for many years after Server 2005 the SQL Server team maintained that FOR XML PATH ('') combined with unnamed columns has undefined behavior (i.e., the behavior is not in the design spec). Even on 2014, you will not see FOR XML PATH ('') used with unnamed columns in the SQL Server doc. FOR XML PATH with unnamed columns, yes. FOR XML PATH ('') with named columns, yes. But those generate different results.

@Bacon Bits 2015-03-23 14:15:21

@Whelkaholism The bottom line is that FOR XML is intended to generate XML, not concatenate arbitrary strings. That's why it escapes &, < and > to XML entity codes (&amp;, &lt;, &gt;). I assume it also will escape " and ' to &quot; and &apos; in attributes as well. It's not GROUP_CONCAT(), string_agg(), array_agg(), listagg(), etc. even if you can kind of make it do that. We should be spending our time demanding Microsoft implement a proper function.

@paio 2015-08-11 13:24:10

I've to use AS 'data()' insted AS [text()].

@Riley Major 2015-10-01 22:00:54

@BaconBits This answer uses better syntax which deals with special characters better by using the TYPE keyword and extracting the contents using the value XML function. Using that method, you can even use tag names and create well formed XML. But you needn't fear columns without names. They are officially supported at least as early as SQL Server 2008.

@Graeme 2016-06-01 20:39:51

To remove the initial comma, I usually use ROW_NUMBER(). Complete sample in an answer below. CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END + stu.Name

@Jason C 2017-04-06 00:32:48

Good news: MS SQL Server will be adding string_agg in v.Next. and all of this can go away.

@rrozema 2017-11-13 15:29:17

Added an improved example to address the issue pointed out by @Sam, characters <, > and & being returned as xml escape sequences &lt;, &gt; and &amp;.

@Jamie Kitson 2017-11-27 11:12:50

You can use STUFF instead of SUBSTRING to remove the leading comma.

@jpaugh 2018-03-19 14:13:10

This is a very poor answer! It doesn't give a simple, runnable example, nor does it explain which parts are necessary to make it work. Could someone who understands it make it better? Maybe you, @P5Coder?

@P5Coder 2018-03-19 18:49:41

@jpaugh why me?

@jpaugh 2018-03-19 20:41:23

I picked you because you have edited it before, and probably understand it. However, I have since found Max's answer to fill that role, and it turns out to use the same SQL trick, although I did not recognize it at first, because of all the noise.

@Maksym Sadovnychyy 2018-06-29 09:39:32

you could add to your example also this part Select Main.SubjectID, CASE WHEN Main.Students Like '%,%' THEN Left(Main.Students,Len(Main.Students)-1) ELSE Main.Students END AS [Students]

@Salman A 2019-01-26 18:49:13

You need to use GROUP BY instead of DISTINCT, it should give you identical results with possibly better performance.

@andynaz 2019-02-01 12:00:30

using AS [text()] make the field be surrounded by the tag <text>... I just remove it and it all works (SQL Server 2008)

@Esperento57 2018-07-31 06:13:40

With a recursive query you can do it:

-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;

-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');

-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank  where rang=1
union all
select f0.*,  cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName 
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1 
)
select AllName from tmpRecursive
where rang=NbRow

@Mathieu Renda 2017-03-14 05:00:15

SQL Server 2017+ and SQL Azure: STRING_AGG

Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.

STRING_AGG (Transact-SQL)

Without grouping

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

With grouping :

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

With grouping and sub-sorting

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;

@canon 2017-07-10 16:17:06

And, unlike CLR solutions, you have control over the sorting.

@user2721607 2017-10-11 20:27:04

This works with SQL Azure. Great answer!

@Kevin Stone 2018-01-04 20:31:41

This also worked for me in Azure SQL. Brilliant!

@Steven Chong 2010-09-09 00:08:06

In SQL Server 2005

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

In SQL Server 2016

you can use the FOR JSON syntax

i.e.

SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

And the result will become

Id  Emails
1   [email protected]
2   NULL
3   [email protected], [email protected]

This will work even your data contains invalid XML characters

the '"},{"_":"' is safe because if you data contain '"},{"_":"', it will be escaped to "},{\"_\":\"

You can replace ', ' with any string separator


And in SQL Server 2017, Azure SQL Database

You can use the new STRING_AGG function

@David 2011-08-11 23:12:59

Good use of the STUFF function to nix the leading two characters.

@R. Schreurs 2013-08-02 08:27:52

I like this solution best, because I can easily use it in a select list by appending 'as <label>'. I am not sure how to do this with the solution of @Ritesh.

@BateTech 2014-04-07 21:35:21

This is better than the accepted answer because this option also handles un-escaping XML reserverd characters such as <, >, &, etc. which FOR XML PATH('') will automatically escape.

@Chris Ward 2018-05-21 14:27:54

This is an awesome response as it resolved the issue and provides the best ways of doing things in different versions of SQL now I wish I could use 2017/Azure

@Aura 2018-04-02 14:40:29

@User1460901 You can try something like this:

WITH cte_base AS (
    SELECT CustomerCode, CustomerName,
    CASE WHEN Typez = 'Breakfast' THEN Items ELSE NULL END AS 'BREAKFAST'
    , CASE WHEN Typez = 'Lunch' THEN Items ELSE NULL END AS 'LUNCH'
    FROM #Customer
    )
    SELECT distinct CustomerCode, CustomerName,
    SUBSTRING(
    (   
        SELECT ','+BREAKFAST AS [text()]
        FROM cte_base b1
        WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
        ORDER BY b1.BREAKFAST
        FOR XML PATH('')
        ), 2, 1000
    ) [BREAKFAST], 
    SUBSTRING(
    (   
        SELECT ','+LUNCH AS [text()]
        FROM cte_base b1
        WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
        ORDER BY b1.LUNCH
        FOR XML PATH('')
        ), 2, 1000
    ) [LUNCH]
    FROM cte_base b2

@Ravi Pipaliya 2018-04-02 13:04:36

Here is the complete solution to achieve this:

-- Table Creation
CREATE TABLE Tbl
( CustomerCode    VARCHAR(50)
, CustomerName    VARCHAR(50)
, Type VARCHAR(50)
,Items    VARCHAR(50)
)

insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'

-- function creation
GO
CREATE  FUNCTION [dbo].[fn_GetItemsByType]
(   
    @CustomerCode VARCHAR(50)
    ,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE  ( Items VARCHAR(5000) )
AS
BEGIN

        INSERT INTO @ItemType(Items)
    SELECT  STUFF((SELECT distinct ',' + [Items]
         FROM Tbl 
         WHERE CustomerCode = @CustomerCode
            AND [email protected]
            FOR XML PATH(''))
        ,1,1,'') as  Items



    RETURN 
END

GO

-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type) 
                    from Tbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
             from 
             (
                select  
                    distinct CustomerCode
                    ,CustomerName
                    ,Type
                    ,F.Items
                    FROM Tbl T
                    CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
            ) x
            pivot 
            (
                max(Items)
                for Type in (' + @cols + ')
            ) p '

execute(@query) 

@Pooja Bhat 2018-02-15 09:35:04

Below is a simple PL/SQL procedure to implement the given scenario using "basic loop" and "rownum"

Table definition

CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;

Let's insert values into this table

INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');

Procedure starts from here

DECLARE 

MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);

BEGIN

SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;

LOOP

SELECT NAME INTO  C_NAME FROM 
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;

NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;

END LOOP;

dbms_output.put_line(SUBSTR(NSTR,2));

END;

Result

PETER,PAUL,MARY

@jpaugh 2018-03-19 13:58:23

The question is asking for an answer specific to SQL Server. If there is a PL/SQL question, you might answer there, instead. However, check out wm_concat, first, and see whether that is an easier method.

@Max Szczurek 2018-01-25 04:55:48

SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')

Here's a sample:

DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary

@jpaugh 2018-03-19 14:21:08

Thanks so much for giving the smallest possible solution, along with a working example! I had no idea why the top-voted answer works, nor how to replicate it.

@Shahbaz 2017-12-07 17:20:21

Although it's too late, and already has many solutions. Here is simple solution for MySQL:

SELECT t1.id,
        GROUP_CONCAT(t1.id) ids
 FROM table t1 JOIN table t2 ON (t1.id = t2.id)
 GROUP BY t1.id

@jpaugh 2018-03-19 13:59:26

This question is specific to SQL server, so this answer is unlikely to be found by those who need it. Is there a mysql-specific question about the same thing?

@pedram 2016-04-05 07:08:21

Use COALESCE - Learn more from here

For an example:

102

103

104

Then write below code in sql server,

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers 
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

Output would be:

102,103,104

@EvilDr 2016-08-03 15:01:16

This is really the best solution IMO as it avoids the encoding issues that FOR XML presents. I used Declare @Numbers AS Nvarchar(MAX) and it worked fine. Can you explain why you recommend not using it please?

@Andre Figueiredo 2017-05-03 21:53:44

This solution has already been posted 8 years ago! stackoverflow.com/a/194887/986862

@Akmal Salikhov 2017-12-07 11:31:13

Why is this query returns ??? symbols instead of Cyrillic ones? Is this just output issue?

@Henrik Fransas 2016-11-21 11:27:29

In SQL Server vNext this will be built in with the STRING_AGG function, read more about it here: https://msdn.microsoft.com/en-us/library/mt790580.aspx

@Tigerjz32 2016-11-15 21:07:57

You need to create a variable that will hold your final result and select into it, like so.

Easiest Solution

DECLARE @char VARCHAR(MAX);

SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];

PRINT @char;

@Glen 2016-06-10 02:03:25

Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking thru the 30 odd answers I still had a twist on a similar answer already given similar to using COALESCE for a single group list and didn't even have to set my variable (defaults to NULL anyhow) and it assumes all entries in my source data table are non blank:

DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData

I am sure COALESCE internally uses the same idea. Lets hope MS don't change this on me.

@Graeme 2016-06-01 20:42:40

--SQL Server 2005+

CREATE TABLE dbo.Students
(
    StudentId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE dbo.Subjects
(
    SubjectId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);

CREATE TABLE dbo.Schedules
(
    StudentId INT
    , SubjectId INT
    , CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
    , CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
    , CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);

INSERT dbo.Students (StudentId, Name) VALUES
    (1, 'Mary')
    , (2, 'John')
    , (3, 'Sam')
    , (4, 'Alaina')
    , (5, 'Edward')
;

INSERT dbo.Subjects (SubjectId, Name) VALUES
    (1, 'Physics')
    , (2, 'Geography')
    , (3, 'French')
    , (4, 'Gymnastics')
;

INSERT dbo.Schedules (StudentId, SubjectId) VALUES
    (1, 1)      --Mary, Physics
    , (2, 1)    --John, Physics
    , (3, 1)    --Sam, Physics
    , (4, 2)    --Alaina, Geography
    , (5, 2)    --Edward, Geography
;

SELECT 
    sub.SubjectId
    , sub.Name AS [SubjectName]
    , ISNULL( x.Students, '') AS Students
FROM
    dbo.Subjects sub
    OUTER APPLY
    (
        SELECT 
            CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
            + stu.Name
        FROM
            dbo.Students stu
            INNER JOIN dbo.Schedules sch
                ON stu.StudentId = sch.StudentId
        WHERE
            sch.SubjectId = sub.SubjectId
        ORDER BY
            stu.Name
        FOR XML PATH('')
    ) x (Students)
;

@Muhammad Bilal 2016-05-26 11:14:20

SELECT PageContent = Stuff(
    (   SELECT PageContent
        FROM dbo.InfoGuide
        WHERE CategoryId = @CategoryId
          AND SubCategoryId = @SubCategoryId
        for xml path(''), type
    ).value('.[1]','nvarchar(max)'),
    1, 1, '')
FROM dbo.InfoGuide info

@Mike Barlow - BarDev 2016-05-04 19:31:22

With the other answers, the person reading the answer must be aware of a specific domain table such as vehicle or student. The table must be created and populated with data to test a solution.

Below is an example that uses SQL Server "Information_Schema.Columns" table. By using this solution, no tables need to be created or data added. This example creates a comma separated list of column names for all tables in the database.

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 

@jens frandsen 2011-04-05 21:19:15

One method not yet shown via the XML data() command in MS SQL Server is:

Assume table called NameList with one column called FName,

SELECT FName + ', ' AS 'data()' 
FROM NameList 
FOR XML PATH('')

returns:

"Peter, Paul, Mary, "

Only the extra comma must be dealt with.

Edit: As adopted from @NReilingh's comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

@Ben 2012-09-07 15:56:00

holy s**t thats amazing! When executed on its own, as in your example the result is formatted as a hyperlink, that when clicked (in SSMS) opens a new window containing the data, but when used as part of a larger query it just appears as a string. Is it a string? or is it xml that i need to treat differently in the application that will be using this data?

@Lukáลก Lánský 2014-02-26 18:34:07

This approach also XML-escapes characters like < and >. So, SELECTing '<b>' + FName + '</b>' results in "&lt;b&gt;John&lt;/b&gt;&lt;b&gt;Paul..."

@Baodad 2014-10-03 22:40:38

Neat solution. I am noticing that even when I do not add the + ', ' it still adds a single space between every concatenated element.

@JP Hellemons 2014-10-13 11:53:40

Seems like the fastest solution. I did not test it and have no metrics available sorry.

@slayernoah 2015-11-18 01:22:05

Neat! Any ideas on how to deal with the comma at the end?

@Konstantin 2015-12-10 14:25:52

Super answer! No deal with stored procedures!

@NReilingh 2016-02-29 18:12:13

@Baodad That appears to be part of the deal. You can workaround by replacing on an added token character. For example, this does a perfect comma-delimited list for any length: SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'')

@NReilingh 2016-02-29 18:28:50

Haha, but at that point you might as well just not use data() in the first place and just do one of the examples above. It seems data() is really just a shorthand for "space delimit this" and if you want something else it's useless -- unless there's a performance impact.

@NReilingh 2016-02-29 18:33:08

Wow, actually in my testing using data() and a replace is WAY more performant than not. Super weird.

@illmortem 2017-07-27 21:00:17

If you replace data() with text() it seems to generate the list without the need to trim spaces out.

@methon.dagger 2019-03-12 09:59:51

Can someone provide an example on how to do this with distinct names?

@hgmnz 2012-08-09 21:20:03

Postgres arrays are awesome. Example:

Create some test data:

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE                                      
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');                                                          
INSERT 0 3
test=# select * from names;
 name  
-------
 Peter
 Paul
 Mary
(3 rows)

Aggregate them in an array:

test=# select array_agg(name) from names;
 array_agg     
------------------- 
 {Peter,Paul,Mary}
(1 row)

Convert the array to a comma delimited string:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

DONE

Since PostgreSQL 9.0 it is even easier.

@Richard Fox 2015-02-27 11:50:37

If you need more than one column, for example their employee id in brackets use the concat operator: select array_to_string(array_agg(name||'('||id||')'

@GoldBishop 2017-05-04 15:03:24

Not applicable to sql-server, only to mysql

@Oleg Sakharov 2011-11-21 01:11:57

I really liked elegancy of Dana's answer. Just wanted to make it complete.

DECLARE @names VARCHAR(MAX)
SET @names = ''

SELECT @names = @names + ', ' + Name FROM Names 

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

@Justin T 2015-12-18 11:04:11

If you are deleting the last two symbols ', ', then you need to add ', ' after Name ('SELECT \@names = \@names + Name + ', ' FROM Names'). That way the last two chars will always be ', '.

@Tian van Heerden 2016-03-04 09:13:29

In my case I needed to get rid of the leading comma so change the query to SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ', ' END + Name FROM Names then you don't have to truncate it afterwards.

@user1767754 2015-07-22 07:51:12

MySQL complete Example:

We have Users which can have many Data's and we want to have an output, where we can see all users Datas in a list:

Result:

___________________________
| id   |  rowList         |
|-------------------------|
| 0    | 6, 9             |
| 1    | 1,2,3,4,5,7,8,1  |
|_________________________|

Table Setup:

CREATE TABLE `Data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);


CREATE TABLE `User` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `User` (`id`) VALUES
(0),
(1);

Query:

SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id

@Nizam 2015-05-08 01:39:06

This answer will require some privilege in server to work.

Assemblies are a good option for you. There are a lot of sites that explain how to create it. The one I think is very well explained is this one

If you want, I have already created the assembly, and it is possible to download the DLL here.

Once you have downloaded it, you will need to run the following script in your SQL Server:

CREATE Assembly concat_assembly 
   AUTHORIZATION dbo 
   FROM '<PATH TO Concat.dll IN SERVER>' 
   WITH PERMISSION_SET = SAFE; 
GO 

CREATE AGGREGATE dbo.concat ( 

    @Value NVARCHAR(MAX) 
  , @Delimiter NVARCHAR(4000) 

) RETURNS NVARCHAR(MAX) 
EXTERNAL Name concat_assembly.[Concat.Concat]; 
GO  

sp_configure 'clr enabled', 1;
RECONFIGURE

Observe that the path to assembly may be accessible to server. Since you have successfully done all the steps, you can use the function like:

SELECT dbo.Concat(field1, ',')
FROM Table1

Hope it helps!!!

@Hamid Bahmanabady 2015-04-20 06:07:42

   declare @phone varchar(max)='' 
   select @[email protected] + mobileno +',' from  members
   select @phone

@shA.t 2015-04-20 07:05:44

Why not +', ' As OP wanted and also you don't delete last ';'. I think this answer is same and also this answer ;).

@Hamid Bahmanabady 2015-04-20 13:24:41

I had this problem and I found answer but I want Concatenate with ';' so I paste it here, last element is empty

@shA.t 2015-04-20 13:43:13

When you post your answer here, It should be related to the question And result of your code should be Null, because you start with @phone IS Null and adding to Null will be Null in SQL Server, I think you forgot something like adding = '' after your first line ;).

@Hamid Bahmanabady 2015-04-20 17:36:29

No, I post answer after check it and result was not null

@Hamid Bahmanabady 2015-04-21 07:50:57

@shA.t you said correct and I changed it, thanks

@Rapunzo 2015-02-12 12:01:27

To avoid null values you can use CONCAT()

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names

@DaveBoltman 2016-09-20 08:15:03

It would be nice to know why CONCAT works. A link to MSDN would be nice.

@a_horse_with_no_name 2012-11-16 23:15:49

Starting with PostgreSQL 9.0 this is quite simple:

select string_agg(name, ',') 
from names;

In versions before 9.0 array_agg() can be used as shown by hgmnz

@Torben Kohlmeier 2013-05-17 12:05:28

To do this with columns that are not of type text, you need to add a type cast: SELECT string_agg(non_text_type::text, ',') FROM table

@a_horse_with_no_name 2013-05-17 12:11:38

@TorbenKohlmeier: you only need that for non-character columns (e.g. integer, decimal). It works just fine for varchar or char

@Yogesh Bhadauirya 2011-07-06 12:46:28

In SQL Server 2005 and later, use the query below to concatenate the rows.

DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t

@Sam 2013-08-13 01:36:56

I believe this fails when the values contain XML symbols such as < or &.

@Max Tkachenko 2013-12-02 09:25:52

With TABLE type it is extremely easy. Let's imagine that your table is called Students and it has column name.

declare @rowsCount INT
declare @i INT = 1
declare @names varchar(max) = ''

DECLARE @MyTable TABLE
(
  Id int identity,
  Name varchar(500)
)
insert into @MyTable select name from Students
set @rowsCount = (select COUNT(Id) from @MyTable)

while @i < @rowsCount
begin
 set @names = @names + ', ' + (select name from @MyTable where Id = @i)
 set @i = @i + 1
end
select @names

This example is tested in MS SQL Server 2008 R2

@topchef 2013-11-15 20:26:03

This method applies to Teradata Aster database only as it utilizes its NPATH function.

Again, we have table Students

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Then with NPATH it is just single SELECT:

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

Result:

SubjectID       StudentName
----------      -------------
1               [John, Mary, Sam]
2               [Alaina, Edward]

@endo64 2013-10-25 08:14:23

This can be useful too

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

returns

Peter,Paul,Mary

@blueling 2013-12-05 09:11:08

Unfortunately this behavior seems not to be officially supported. MSDN says: "If a variable is referenced in a select list, it should be assigned a scalar value or the SELECT statement should only return one row." And there are people who observed problems: sqlmag.com/sql-server/multi-row-variable-assignment-and-orde‌​r

Related Questions

Sponsored Content

27 Answered Questions

43 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

29 Answered Questions

[SOLVED] How to concatenate string variables in Bash

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

20 Answered Questions

[SOLVED] Get size of all tables in database

  • 2011-10-25 16:14:34
  • Eric
  • 1126774 View
  • 1090 Score
  • 20 Answer
  • Tags:   sql-server tsql

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

17 Answered Questions

Sponsored Content