Concatenate a string from multiple rows in SQL Server

Question

I want to create a view where an entry in my view is a concatenated string. In this case, it is ParticipantNames.

I have two tables. A Trips table and a Participants table. One trip can have one to many participants. So in my Participant table I have multiple rows that are related to a single entry in the Trip table.

Is it possible to create a string that concatenates the names from all the participants and insert that into the ParticipantNames column? e.g "Hans, Ben, Ali"

CREATE OR ALTER VIEW TripView 
AS
    SELECT
        Id  = Trip .Id,
        ParticipantNames = ???
    FROM    
        [dbo].Trip Trip 
    LEFT JOIN 
        [dbo].[Participants] Participants ON Participants.TripId = Trip.Id

Gordon Linoff · Accepted Answer · 2020-03-22 23:56:00Z

3

You can use string_agg() in the more recent versions of SQL Server:

 SELECT t.Id, string_agg(p.name, ', ') as ParticipantNames
 FROM  [dbo].Trip t LEFT JOIN 
       [dbo].[Participants] p
       ON p.TripId = t.Id
 GROUP BY t.Id;

answered Mar 22, 2020 at 23:56

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

XRaycat Over a year ago

Where should i write this? If i write it above where i am creating my view it says "CREATE VIEW MUST BE THE ONLY STATEMENT IN THIS BATCH"

Gordon Linoff Over a year ago

You need a GO before the CREATE VIEW.

XRaycat Over a year ago

Okay..and the thing you wrote where should i place it? It says: A fatal scripting error occurred. Incorrect syntax was encountered while parsing GO.

Gordon Linoff Over a year ago

@Xraycat922 . .. This query seems syntactically correct.

XRaycat Over a year ago

To answer my own question - yes you can.

|

marc_s · Accepted Answer · 2020-03-23 05:26:24Z

3

For versions earlier than SQL Server 2017, you can use FOR XML PATH, for string concatenation

SELECT 
    T.Id, 
    STUFF((SELECT ','+p.name
           FROM dbo.participants p
           WHERE p.TripId = T.Id
           FOR XML PATH('')), 1, 1, '') AS ParticipantNames
FROM [dbo].Trip T
GROUP BY T.Id;

From SQL Server 2017 onwards, you can go ahead with Gordon Linoff solution.

edited Mar 23, 2020 at 5:26

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 23, 2020 at 4:46

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Collectives™ on Stack Overflow

Concatenate a string from multiple rows in SQL Server

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related