CODEWITHSUNDEEP
sqlsql-servert-sql
XRaycat
XRaycat

Reputation: 1140

Concatenate a string from multiple rows in SQL Server

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

Upvotes: 0

Views: 10135

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 3

Related Questions