However to concatenate matter from aggregate rows into a azygous matter drawstring successful SQL Server

See a database array holding names, with 3 rows:

PeterPaulMary

Is location an casual manner to bend this into a azygous drawstring of Peter, Paul, Mary?

If you are connected SQL Server 2017 oregon Azure, seat Mathieu Renda reply.

I had a akin content once I was making an attempt to articulation 2 tables with 1-to-galore relationships. Successful SQL 2005 I recovered that XML PATH methodology tin grip the concatenation of the rows precise easy.

If location is a array referred to as STUDENTS

SubjectID StudentName---------- -------------1 Mary1 John1 Sam2 Alaina2 Edward

Consequence I anticipated was:

SubjectID StudentName---------- -------------1 Mary, John, Sam2 Alaina, Edward

I utilized the pursuing T-SQL:

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

You tin bash the aforesaid happening successful a much compact manner if you tin concat the commas astatine the opening and usage stuff to skip the archetypal 1 truthful you don't demand to bash a sub-question:

SELECT ST2.SubjectID, STUFF( ( SELECT ',' + ST1.StudentName AS [text()] FROM dbo.Students ST1 WHERE ST1.SubjectID = ST2.SubjectID ORDER BY ST1.SubjectID FOR XML PATH (''), TYPE ).value('text()[1]','nvarchar(max)'), 1, 1, '') [Students]FROM dbo.Students ST2GROUP BY ST2.SubjectID

This reply whitethorn instrument surprising outcomes For accordant outcomes, usage 1 of the FOR XML Way strategies elaborate successful another solutions.

Usage COALESCE:

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

Conscionable any mentation (since this reply appears to acquire comparatively daily views):

• Coalesce is truly conscionable a adjuvant cheat that accomplishes 2 issues:

1) Nary demand to initialize @Names with an bare drawstring worth.

2) Nary demand to part disconnected an other separator astatine the extremity.

• The resolution supra volition springiness incorrect outcomes if a line has a NULL Sanction worth (if location is a NULL, the NULL volition brand @Names NULL last that line, and the adjacent line volition commencement complete arsenic an bare drawstring once more. Easy mounted with 1 of 2 options:

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + NameFROM PeopleWHERE Name IS NOT NULL

oregon:

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

Relying connected what behaviour you privation (the archetypal action conscionable filters NULLs retired, the 2nd action retains them successful the database with a marker communication [regenerate 'N/A' with any is due for you]).

Mistake producing weblog contented