CODEWITHSUNDEEP
sqlsql-server
LeroyJenkins608
LeroyJenkins608

Reputation: 43

SQL Server Query: Concatenate Multiple Rows during a JOIN

This may have been mentioned here before, but I couldn't find an answer to the exact problem I'm trying to solve. I am using SSMS 2018 v18.8 with SQL Server 2012 SP4. My query requires querying multiple tables with joins. One table (table 2) contains multiple values, and I'd like to return them in the query as a concatenated line.

enter image description here

How should I write my query to return the desired output?

Upvotes: 1

Views: 2214

Answers (2)

Jimmy T.
Jimmy T.

Reputation: 121

Very simple answer using US States and Counties, Counties table contains a key (StateID) identifying the State the County belongs to.

SELECT States.name, STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(County,'N/A')), ', ') AS Counties
FROM Counties
join States on Counties.StateID=State.ID
GROUP BY States.name;

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

Normally, you want the client application or reporting tool to roll up data like this. However, in Sql Server 2017 and later you can use the string_agg() function (along with GROUP BY) to make this happen.

SELECT t1.System, t1.Location, string_agg(t2.User, ',') As [User]
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.System=t2.System
GROUP BY t1.System, t1.Location

Earlier versions need an ugly FOR XML PATH query.

Upvotes: 2

Related Questions