Verkettung von Text aus mehreren Zeilen in eine einzige Textzeichenfolge in SQL Server

Question

Stellen Sie sich eine Datenbanktabelle mit Namen vor, die drei Zeilen umfasst:

Peter
Paul
Mary

Gibt es eine einfache Möglichkeit, dies in eine einzige Zeichenfolge von Peter, Paul, Mary ?

Answer 1

Wenn Sie mit SQL Server 2017 oder Azure arbeiten, siehe Mathieu Renda Antwort .

Ich hatte ein ähnliches Problem, als ich versuchte, zwei Tabellen mit Eins-zu-Viel-Beziehungen zu verbinden. In SQL 2005 habe ich festgestellt, dass XML PATH Methode kann die Verkettung der Zeilen sehr einfach handhaben.

Wenn es eine Tabelle namens STUDENTS

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

Das Ergebnis, das ich erwartet hatte, war:

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

Ich habe Folgendes verwendet T-SQL :

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]

Sie können dasselbe auf kompaktere Weise tun, wenn Sie die Kommas am Anfang zusammenfassen und die substring um die erste Abfrage zu überspringen, damit Sie keine Unterabfrage machen müssen:

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

Answer 2

Diese Antwort kann Folgendes ergeben unerwartete Ergebnisse Um konsistente Ergebnisse zu erzielen, verwenden Sie eine der in anderen Antworten beschriebenen FOR XML PATH-Methoden.

Verwenden Sie COALESCE :

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

Nur eine kleine Erklärung (da diese Antwort anscheinend relativ häufig aufgerufen wird):

• Coalesce ist eigentlich nur ein hilfreicher Cheat, der zwei Dinge erreicht:

1) Keine Notwendigkeit der Initialisierung @Names mit einem leeren Stringwert.

2) Es ist nicht nötig, ein zusätzliches Trennzeichen am Ende zu entfernen.

• Die obige Lösung wird falsche Ergebnisse liefern, wenn eine Zeile ein NULL Namenswert (wenn es einen NULL は、その NULL wird machen @Names NULL nach dieser Zeile, und die nächste Zeile beginnt wieder mit einer leeren Zeichenkette. Dies lässt sich leicht mit einer von zwei Lösungen beheben:

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

oder:

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

Je nachdem, welches Verhalten Sie wünschen (die erste Option filtert nur NULL s aus, bei der zweiten Option bleiben sie in der Liste mit einer Markierungsmeldung [ersetzen Sie 'N/A' durch eine für Sie geeignete Angabe]).

Answer 3

SQL Server 2017+ und SQL Azure: STRING_AGG

Ab der nächsten Version von SQL Server können wir endlich zeilenübergreifende Verkettungen vornehmen, ohne auf Variablen oder XML-Zauberei zurückgreifen zu müssen.

STRING_AGG (Transact-SQL)

Ohne Gruppierung

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

Mit Gruppierung:

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

Mit Gruppierung und Untersortierung

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

Answer 4

Eine noch nicht gezeigte Methode ist die XML data() Befehl in SQL Server ist:

Nehmen wir eine Tabelle namens NameList mit einer Spalte namens FName an,

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

zurück:

"Peter, Paul, Mary, "

Lediglich das zusätzliche Komma muss beachtet werden.

Wie von @NReilingh's Kommentar übernommen, können Sie die folgende Methode verwenden, um das nachgestellte Komma zu entfernen. Angenommen, die Tabellen- und Spaltennamen sind identisch:

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

Answer 5

En SQL Server 2005

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

---

In SQL Server 2016

können Sie die FÜR JSON-Syntax

d.h.

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

Und das Ergebnis wird sein

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

Dies funktioniert auch, wenn Ihre Daten ungültige XML-Zeichen enthalten

その '"},{"_":"' ist sicher, denn wenn Sie Daten enthalten '"},{"_":"', es wird entkommen nach "},{\"_\":\"

Sie können ersetzen ', ' mit einem beliebigen Zeichentrennzeichen

---

Und in SQL Server 2017, Azure SQL Database

Sie können die neue STRING_AGG-Funktion