Comment concrétiser le texte de plusieurs lignes en une seule chaîne de texte dans SQL Server

93

SubjectID       StudentName ----------      ------------- 1               Mary 1               John 1               Sam 2               Alaina 2               Edward 

SubjectID       StudentName ----------      ------------- 1               Mary, John, Sam 2               Alaina, Edward 

SELECT Main.SubjectID,        LEFT(Main.Students,Len(Main.Students)-1) As "Students" FROM     (         SELECT DISTINCT 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     ) [Main] 

SELECT DISTINCT ST2.SubjectID,      SUBSTRING(         (             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)'), 2, 1000) [Students] FROM dbo.Students ST2 

80

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

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

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

71

SELECT STRING_AGG(Name, ', ') AS Departments FROM HumanResources.Department; 

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments FROM HumanResources.Department GROUP BY GroupName; 

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments FROM HumanResources.Department GROUP BY GroupName; 

66

SELECT FName + ', ' AS 'data()' FROM NameList FOR XML PATH('') 

"Peter, Paul, Mary, " 

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands 

52

SELECT Stuff(   (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)   .value('text()[1]','nvarchar(max)'),1,2,N'') 

SELECT per.ID, Emails = JSON_VALUE(    REPLACE(      (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)     ,'"},{"_":"',', '),'$[0]._' )  FROM Person per 

Id  Emails 1   abc@gmail.com 2   NULL 3   def@gmail.com, xyz@gmail.com