verketten - t sql group by concatenate string




Wie verkette ich Text aus mehreren Zeilen in einer einzigen Textzeichenfolge in SQL Server? (20)

In 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

Sie können die FOR JSON-Syntax verwenden

dh

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   [email protected]
2   NULL
3   [email protected], [email protected]

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

Das ''}, {' ': '' ist sicher, denn wenn Ihre Daten ''}, {" ": "'enthalten, wird es zu"}, {\ "_ \": \ "

Sie können ',' mit einem beliebigen Trennzeichen für Zeichenfolgen ersetzen

Und in SQL Server 2017 Azure SQL-Datenbank

Sie können die neue Funktion STRING_AGG verwenden

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

Peter
Paul
Mary

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


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-Zauberprogramme zurückgreifen zu müssen.

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;

Ab PostgreSQL 9.0 ist das ganz einfach:

select string_agg(name, ',') 
from names;

In Versionen vor 9.0 kann array_agg() wie von hgmnz gezeigt verwendet werden


Bei den anderen Antworten muss die Person, die die Antwort liest, eine bestimmte Domain-Tabelle wie Fahrzeug oder Student kennen. Die Tabelle muss erstellt und mit Daten gefüllt werden, um eine Lösung zu testen.

Unten ist ein Beispiel, das SQL Server "Information_Schema.Columns" -Tabelle verwendet. Bei Verwendung dieser Lösung müssen keine Tabellen erstellt oder Daten hinzugefügt werden. In diesem Beispiel wird eine durch Kommas getrennte Liste von Spaltennamen für alle Tabellen in der Datenbank erstellt.

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 

Dies kann auch nützlich sein

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

kehrt zurück

Peter,Paul,Mary

Diese Antwort erfordert einige Privilegien auf dem Server zu arbeiten.

Assemblies sind eine gute Option für Sie. Es gibt eine Menge Websites, die erklären, wie man es erstellt. Der eine, den ich denke, ist sehr gut erklärt, ist dieser

Wenn Sie möchten, habe ich bereits die Assembly erstellt, und es ist möglich, die DLL here herunterzuladen.

Sobald Sie es heruntergeladen haben, müssen Sie das folgende Skript in Ihrem SQL Server ausführen:

CREATE Assembly concat_assembly 
   AUTHORIZATION dbo 
   FROM '<PATH TO Concat.dll IN SERVER>' 
   WITH PERMISSION_SET = SAFE; 
GO 

CREATE AGGREGATE dbo.concat ( 

    @Value NVARCHAR(MAX) 
  , @Delimiter NVARCHAR(4000) 

) RETURNS NVARCHAR(MAX) 
EXTERNAL Name concat_assembly.[Concat.Concat]; 
GO  

sp_configure 'clr enabled', 1;
RECONFIGURE

Beachten Sie, dass der Pfad zur Assembly möglicherweise für den Server zugänglich ist. Da Sie alle Schritte erfolgreich ausgeführt haben, können Sie die Funktion wie folgt verwenden:

SELECT dbo.Concat(field1, ',')
FROM Table1

Ich hoffe es hilft!!!


Eine gebrauchsfertige Lösung ohne zusätzliche Kommas:

select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

Eine leere Liste führt zu einem NULL-Wert. Normalerweise fügen Sie die Liste in eine Tabellenspalte oder Programmvariable ein: passen Sie die maximale Länge von 255 an Ihre Bedürfnisse an.

(Diwakar und Jens Frandsen haben gute Antworten gegeben, müssen aber verbessert werden.)


Eine rekursive CTE-Lösung wurde vorgeschlagen, aber kein Code zur Verfügung gestellt. Der folgende Code ist ein Beispiel für einen rekursiven CTE. Beachten Sie, dass die Ergebnisse zwar der Frage entsprechen, die Daten jedoch nicht ganz der angegebenen Beschreibung entsprechen, da ich davon ausgehe, dass Sie dies wirklich in Gruppen von Zeilen tun möchten, nicht alle Zeilen in der Tabelle. Wenn Sie es so ändern, dass es allen Zeilen in der Tabelle entspricht, bleibt es für den Leser eine Übung.

;with basetable as 
(   SELECT id, CAST(name as varchar(max))name, 
        ROW_NUMBER() OVER(Partition By id     order by seq) rw, 
        COUNT(*) OVER (Partition By id) recs 
FROM (VALUES (1, 'Johnny', 1), (1,'M', 2), 
                  (2,'Bill', 1), (2, 'S.', 4), (2, 'Preston', 5), (2, 'Esq.', 6),
        (3, 'Ted', 1), (3,'Theodore', 2), (3,'Logan', 3),
                  (4, 'Peter', 1), (4,'Paul', 2), (4,'Mary', 3)

           )g(id, name, seq)
),
rCTE as (
    SELECT recs, id, name, rw from basetable where rw=1
    UNION ALL
    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw+1
    FROM basetable b
         inner join rCTE r
    on b.id = r.id and b.rw = r.rw+1
)
SELECT name FROM rCTE
WHERE recs = rw and ID=4

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

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 das folgende 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]

Sie können das gleiche auf eine kompaktere Weise tun, wenn Sie die Kommas am Anfang zusammenfassen und substring , um den ersten zu überspringen, 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

Ich mochte wirklich die Eleganz von Danas Antwort . Ich wollte es nur vervollständigen.

DECLARE @names VARCHAR(MAX)
SET @names = ''

SELECT @names = @names + ', ' + Name FROM Names 

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

In Oracle ist es wm_concat . Ich glaube, dass diese Funktion in der 10g-Version und höher verfügbar ist.



Normalerweise verwende ich select wie folgt, um Strings in SQL Server zu verketten:

with lines as 
( 
  select 
    row_number() over(order by id) id, -- id is a line id
    line -- line of text.
  from
    source -- line source
), 
result_lines as 
( 
  select 
    id, 
    cast(line as nvarchar(max)) line 
  from 
    lines 
  where 
    id = 1 
  union all 
  select 
    l.id, 
    cast(r.line + N', ' + l.line as nvarchar(max))
  from 
    lines l 
    inner join 
    result_lines r 
    on 
      l.id = r.id + 1 
) 
select top 1 
  line
from
  result_lines
order by
  id desc

Oracle 11g Release 2 unterstützt die Funktion LISTAGG. Dokumentation here .

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

Warnung

Seien Sie vorsichtig bei der Implementierung dieser Funktion, wenn die Möglichkeit besteht, dass die resultierende Zeichenfolge über 4000 Zeichen lang ist. Es wird eine Ausnahme ausgelöst. Wenn dies der Fall ist, müssen Sie entweder die Ausnahme behandeln oder eine eigene Funktion ausführen, die verhindert, dass die verknüpfte Zeichenfolge mehr als 4000 Zeichen enthält.


Sie müssen eine Variable erstellen, die Ihr Endergebnis enthält und wie folgt in das Endergebnis einfügt.

Einfachste Lösung

DECLARE @char VARCHAR(MAX);

SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];

PRINT @char;

Um Nullwerte zu vermeiden, können Sie CONCAT () verwenden

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names

Verwenden Sie COALESCE - 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

Verwenden Sie in SQL Server 2005 und höher die folgende Abfrage zum Verketten der Zeilen.

DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t

MySQL vollständig Beispiel:

Wir haben Benutzer, die viele Daten haben können und wir möchten eine Ausgabe haben, wo wir alle Benutzer Daten in einer Liste sehen können:

Ergebnis:

___________________________
| id   |  rowList         |
|-------------------------|
| 0    | 6, 9             |
| 1    | 1,2,3,4,5,7,8,1  |
|_________________________|

Tabelleneinrichtung:

CREATE TABLE `Data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);


CREATE TABLE `User` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `User` (`id`) VALUES
(0),
(1);

Abfrage:

SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id

DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

Dies setzt das Streukomma am Anfang.

Wenn Sie jedoch andere Spalten oder eine untergeordnete CSV-Tabelle benötigen, müssen Sie diese in ein benutzerdefiniertes skalares Feld (UDF) einbinden.

Sie können den XML-Pfad auch als korrelierte Unterabfrage in der SELECT-Klausel verwenden (aber ich würde warten müssen, bis ich wieder zur Arbeit gehe, weil Google keine Arbeit zu Hause erledigt :-)





group-concat