Frage Wie verkette ich Text aus mehreren Zeilen in einer einzigen Textzeichenfolge in SQL Server?


Betrachten Sie eine Datenbanktabelle, die Namen mit drei Zeilen enthält:

Peter
Paul
Mary

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


1483
2017-10-11 23:49


Ursprung


Antworten:


Wenn Sie SQL Server 2017 oder Azure verwenden, siehe Mathieu Renda antwortet.

Ich hatte ein ähnliches Problem, als ich versuchte, zwei Tische mit Eins-zu-Viele-Beziehungen zu verbinden. In SQL 2005 habe ich das gefunden 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

Ergebnis, das ich erwartet habe, war:

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

Ich habe folgendes benutzt 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 ('')
            ) [Students]
        From dbo.Students ST2
    ) [Main]

Das gleiche können Sie kompakter machen, wenn Sie die Kommas zu Beginn verwenden und verwenden können substring Überspringen Sie den ersten, so dass Sie keine Unterabfrage durchführen 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 ('')
        ), 2, 1000) [Students]
From dbo.Students ST2

1105
2018-02-13 11:53



Diese Antwort kann zu unerwarteten Ergebnissen führen, wenn eine ORDER BY-Klausel vorhanden ist. Verwenden Sie für konsistente Ergebnisse eine der in anderen Antworten beschriebenen FOR XML PATH-Methoden.

Benutzen COALESCE:

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

Nur eine Erklärung (da diese Antwort scheinbar relativ regelmäßig erscheint):

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

1) Keine Notwendigkeit zu initialisieren @Names mit einem leeren String-Wert.

2) Keine Notwendigkeit, einen zusätzlichen Separator am Ende abzustreifen.

  • Die obige Lösung wird falsche Ergebnisse liefern, wenn eine Zeile eine hat NULL Name Wert (wenn es ein NULL, das NULL wird machen @Names  NULL nach dieser Zeile, und die nächste Zeile wird wieder als leere Zeichenfolge beginnen. Einfach mit einer von zwei Lösungen zu 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

Abhängig davon, welches Verhalten Sie wünschen (die erste Option filtert nur NULLs out, die zweite Option hält sie in der Liste mit einer Marker-Nachricht [ersetzen 'N / A' mit dem, was für Sie geeignet ist]).


893
2017-10-12 00:18



Eine Methode, die noch nicht über die XML  data() Befehl in MS SQL Server ist:

Angenommen, die Tabelle NameList heißt mit einer Spalte namens FName.

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

kehrt zurück:

"Peter, Paul, Mary, "

Nur das zusätzliche Komma muss behandelt werden.

Bearbeiten: Wie aus @ NReilingh's Kommentar übernommen, können Sie das folgende Komma mit der folgenden Methode entfernen. Unter der Annahme der gleichen Tabellen- und Spaltennamen:

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

296
2018-04-05 21:19



Im 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

du kannst den ... benutzen 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 werden

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

das '"},{"_":"' ist sicher, wenn Sie Daten enthalten '"},{"_":"', es wird zu entkommen "},{\"_\":\"

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


Und in SQL Server 2017 Azure SQL-Datenbank

Sie können das neue verwenden STRING_AGG-Funktion


214
2018-03-14 05:00



SQL Server 2017 und SQL Azure: STRING_AGG

Beginnend mit der nächsten Version von SQL Server können wir schließlich über mehrere Zeilen hinweg verketten, ohne auf Variablen oder XML-Hexeries 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;

167
2017-10-12 00:10



In MySQL gibt es eine Funktion, GROUP_CONCAT (), mit dem Sie die Werte mehrerer Zeilen verketten können. Beispiel:

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a

98
2018-04-05 07:08



Benutzen VERSCHMELZEN - Erfahren Sie mehr von hier

Zum Beispiel:

102

103

104

Dann schreibe unten Code in SQL-Server,

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers 
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

Ausgabe wäre:

102,103,104

52
2018-03-08 16:29