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

Mots clés : sqlsql-servercsvstring-concatenationgroup-concatsql

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

vote vote

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 
vote vote

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 
vote vote

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; 
vote vote

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 
vote vote

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 

Questions similaires