How to concatenate text from multiple rows into a single text string in SQL Server

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

In SQL Server 2017 or later versions, you can concatenate text from multiple rows into a single text string in SQL Server using the STRING_AGG function. Here's an example of how you can achieve this:

Assuming you have a table called "Names" with a column "Name" containing the values Peter, Paul, and Mary in separate rows, you can use the following SQL query:

SELECT STRING_AGG(Name, ', ') AS ConcatenatedNames
FROM Names;

This query will return a single string with the names concatenated and separated by commas:

ConcatenatedNames
-----------------
Peter, Paul, Mary

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 Type=@Type
            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) 

This method applies to the Teradata Aster database only as it uses 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]

SQL Server 2005 or later

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)
;

Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking through 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. Let’s hope Microsoft don't change this on me.

It's now 2024, and a lot of the Answers shown here from previous years are a bit out-of-date. With SQL Server, you can now easily combine fields using STRING_AGG.

Let's look at a simple example.

In my database, I have a table of Users, a table of possible Roles, and a third table showing which Users have which Roles.

Here's how I would get a list of user names, followed by a comma-seperated list of their roles:

SELECT usr.Id, usr.UserName, STRING_AGG(rol.Name, ', ') AS 'Roles'
FROM dbo.[User] usr
LEFT JOIN [dbo].[RoleUser] ru
ON ru.UserId = usr.Id
LEFT JOIN [dbo].[Role] rol
ON rol.Id = ru.RoleId
GROUP BY usr.Id, usr.UserName
ORDER BY usr.Id

And here's what it would look like:

One way you could do it in SQL Server would be to return the table content as XML (for XML raw), convert the result to a string and then replace the tags with ", ".

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

In PostgreSQL - array_agg

SELECT array_to_string(array_agg(DISTINCT rolname), ',') FROM pg_catalog.pg_roles;

Or STRING_AGG

SELECT STRING_AGG(rolname::text,',') FROM pg_catalog.pg_roles;

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

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

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

Use this:

ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'

Where the "300" could be any width taking into account the maximum number of items you think will show up.

There are a couple of ways in Oracle:

    create table name
    (first_name varchar2(30));

    insert into name values ('Peter');
    insert into name values ('Paul');
    insert into name values ('Mary');

Solution is 1:

    select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
    o/p=> Peter,Paul,Mary

Solution is 2:

    select  rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
    o/p=> Peter,Paul,Mary

With the '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 was tested with SQL Server 2008 R2.

Here I am showing about if it is more than 2 columns table data, then how to solve the problem.

If you have SQL Server Management Studio 2017 or later, you can very easily solve it using

STRING_AGG

Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.

Below is the SQL query

DECLARE @tbl TABLE (id INT, _name CHAR(1), days VARCHAR(10), daysfrequency VARCHAR(10), scheduletime VARCHAR(10));
INSERT INTO @tbl (id,_name,days,daysfrequency,scheduletime) VALUES
(1, 'a', 'Day4','Monthly','22:10'),
(1, 'a', 'Thu', 'Weekly', '07:30'),
(1, 'a', 'Fri', 'Daily',  '23:10'),
(2, 'b', 'Mon', 'Weekly', '20:00'),
(2, 'b', 'Tue', 'Weekly', '23:10'),
(2, 'b', 'Wed', 'Weekly', '18:10'),
(2, 'b', 'Thu', 'Weekly', '10:23'),
(2, 'b', 'Fri', 'Weekly', '1:23');


SELECT  t.id,
        t._name,
        STRING_AGG(t.days, ', ') WITHIN GROUP (ORDER BY t._name ASC) AS days,
        t1.daysfrequency,
        STRING_AGG(t.scheduletime, ', ') WITHIN GROUP (ORDER BY t._name ASC) AS scheduletime
FROM @tbl t INNER JOIN
(
    SELECT id, _name, STRING_AGG(daysfrequency, ', ') WITHIN GROUP (ORDER BY _name ASC) AS daysfrequency FROM
    (
        SELECT DISTINCT id, _name, daysfrequency FROM @tbl
    ) a
    GROUP BY id, _name
) t1
ON t.id = t1.id AND t._name = t1._name
GROUP BY t.id, t._name, t1.daysfrequency;

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

First of all you should declare a table variable and fill it with your table data and after that, with a WHILE loop, select row one by one and add its value to a nvarchar(max) variable.

    Go
    declare @temp table(
        title nvarchar(50)
    )
    insert into @temp(title)
    select p.Title from dbo.person p
    --
    declare @mainString nvarchar(max)
    set @mainString = '';
    --
    while ((select count(*) from @temp) != 0)
    begin
        declare @itemTitle nvarchar(50)
        set @itemTitle = (select top(1) t.Title from @temp t)
    
        if @mainString = ''
        begin
            set @mainString = @itemTitle
        end
        else
        begin
            set @mainString = concat(@mainString,',',@itemTitle)
        end
    
        delete top(1) from @temp
    
    end
    print @mainString

I don't have the reputation to comment on the answers warning about using varchar(max), so posting as an answer with full supporting documentation.

If your variable is defined such as varchar(max), you will run into performance issues if the resulting variable length is greater than 256k. It'll append each value in <2 ms up to that point when utilized with a table select, then the updates will take 200x times as long each time you append once you cross that 256k threshold, increasing in time as the size increases.

To work around that limit, create a temp table that you insert rows into instead, then use the XML method covered in many other posts to create the resulting value.

Demonstrating the performance problem:

-- Takes < 45 sec to run to build a variable 512k in size using the variable appending method
SET NOCOUNT ON
DECLARE @txt nvarchar(max)='',
        @StartTime datetime2,
        @MS int,
        @k int
DECLARE @Stats TABLE (
    id int NOT NULL IDENTITY PRIMARY KEY,
    k int,
    ms int
)

WHILE Len(@txt) / 1024 < 512
BEGIN
    SET @StartTime=SYSDATETIME()
    SELECT TOP 28 @txt=@txt + cast(rowguid as char(36))
    FROM AdventureWorks2022.Person.Person
    SET @MS=DATEDIFF(ms,@StartTime,SYSDATETIME())
    SET @k=Len(@txt) / 1024
    IF NOT EXISTS (SELECT 1 FROM @Stats WHERE k=@k)
        INSERT INTO @Stats VALUES (@k,@MS)
END

SELECT ms FROM @Stats ORDER BY id

The below SQL creates a variable with over a MB of data ranging from 80ms to 150ms total on the same laptop as the above query. You will have to unencode any < and > and check for any other character encoding in your result. If this process is inside a loop, be sure to delete all rows from the temp table after extracting it's data. Also note your delimiter will have a trailing space added to it.

-- Takes < 1 sec to run
SET NOCOUNT ON
DECLARE @txt nvarchar(max)='',
    @StartTime datetime2,
    @MS int
DROP TABLE IF EXISTS #Hold
CREATE TABLE #Hold (
    id int NOT NULL IDENTITY PRIMARY KEY,
    txt nvarchar(102)
)

SET @StartTime=SYSDATETIME()

INSERT INTO #Hold
SELECT cast(rowguid as char(36)) + N','
FROM AdventureWorks2022.Sales.SalesOrderHeader

SET @txt=(SELECT txt AS 'data()' FROM #Hold ORDER BY id FOR XML PATH(''))

SET @MS=DATEDIFF(ms,@StartTime,SYSDATETIME())

SELECT @MS as MS, Len(@txt) as TxtLen