Vraag Hoe kan tekst uit meerdere rijen worden samengevoegd tot een enkele tekststring in een SQL-server?


Beschouw een databasetabel met namen, met drie rijen:

Peter
Paul
Mary

Is er een eenvoudige manier om dit in een enkele reeks te veranderen Peter, Paul, Mary?


1483
2017-10-11 23:49


oorsprong


antwoorden:


Zie als u SQL Server 2017 of Azure gebruikt Mathieu Renda antwoord.

Ik had een soortgelijk probleem toen ik probeerde aan twee tafels deel te nemen met een-op-veel relaties. In SQL 2005 heb ik dat gevonden XML PATH methode kan de aaneenschakeling van de rijen heel gemakkelijk aan.

Als er een tabel is genaamd STUDENTS

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

Het verwachte resultaat was:

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

Ik gebruikte het volgende 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]

U kunt hetzelfde doen op een meer compacte manier als u de komma's bij het begin en gebruik kunt concateren substring om de eerste over te slaan, zodat u geen sub-query hoeft uit te voeren:

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



Dit antwoord kan onverwachte resultaten opleveren wanneer een ORDER BY-component aanwezig is. Gebruik voor consistente resultaten een van de FOR XML PATH-methoden die in andere antwoorden zijn beschreven.

Gebruik COALESCE:

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

Slechts een verklaring (aangezien dit antwoord relatief regelmatig wordt weergegeven):

  • Coalesce is eigenlijk gewoon een handige cheat die twee dingen volbrengt:

1) Niet nodig om te initialiseren @Names met een lege tekenreekswaarde.

2) Het is niet nodig om aan het eind een extra scheider te verwijderen.

  • De bovenstaande oplossing geeft onjuiste resultaten als een rij een heeft NUL Naamwaarde (als er een is NUL, de NUL zal maken @Names  NUL na die rij, en de volgende rij opnieuw opnieuw beginnen als een lege reeks. Eenvoudig te repareren met een van de twee oplossingen:
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

of:

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

Afhankelijk van welk gedrag je wilt (de eerste optie filtert alleen NULs uit, de tweede optie houdt ze in de lijst met een markeringsbericht [vervang 'N / A' met wat voor u geschikt is)).


893
2017-10-12 00:18



Eén methode nog niet getoond via de XML  data() opdracht in MS SQL Server is:

Stel tabel genaamd NameList met één kolom genaamd FName,

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

komt terug:

"Peter, Paul, Mary, "

Alleen de extra komma moet worden afgehandeld.

Bewerk: Zoals overgenomen van de opmerking van @ NReilingh, kunt u de volgende methode gebruiken om de achterliggende komma te verwijderen. Uitgaande van dezelfde tabel- en kolomnamen:

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

296
2018-04-05 21:19



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

je kunt de gebruiken VOOR JSON-syntaxis

d.w.z.

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

En het resultaat zal worden

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

Dit werkt zelfs als uw gegevens ongeldige XML-tekens bevatten

de '"},{"_":"' is veilig omdat als je gegevens bevatten '"},{"_":"', het zal worden ontsnapt "},{\"_\":\"

Je kunt vervangen ', ' met een stringscheider


En in SQL Server 2017 Azure SQL Database

U kunt het nieuwe gebruiken STRING_AGG-functie


214
2018-03-14 05:00



SQL Server 2017+ en SQL Azure: STRING_AGG

Te beginnen met de volgende versie van SQL Server, kunnen we eindelijk aaneenschakelen tussen rijen zonder gebruik te maken van een variabele of XML-witchery.

STRING_AGG (Transact-SQL)

Zonder te groeperen

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

Met groepering:

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

Met groeperen en sub-sorteren

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 is er een functie, GROUP_CONCAT (), waarmee u de waarden uit meerdere rijen kunt samenvoegen. Voorbeeld:

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



Gebruik COALESCE - Meer informatie van hier

Bijvoorbeeld:

102

103

104

Schrijf vervolgens onderstaande code in de 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

Uitvoer zou zijn:

102,103,104

52
2018-03-08 16:29



Oracle 11g Release 2 ondersteunt de LISTAGG-functie. Documentatie hier.

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.

Waarschuwing

Wees voorzichtig met het implementeren van deze functie als de mogelijkheid bestaat dat de resulterende reeks meer dan 4000 tekens bevat. Het zal een uitzondering werpen. Als dat het geval is, moet u de uitzondering afhandelen of uw eigen functie gebruiken om te voorkomen dat de gekoppelde tekenreeks meer dan 4000 tekens bevat.


42
2017-08-09 21:20



Postgres-arrays zijn geweldig. Voorbeeld:

Maak een aantal testgegevens:

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE                                      
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');                                                          
INSERT 0 3
test=# select * from names;
 name  
-------
 Peter
 Paul
 Mary
(3 rows)

Aggregeer ze in een array:

test=# select array_agg(name) from names;
 array_agg     
------------------- 
 {Peter,Paul,Mary}
(1 row)

Converteer de array naar een door komma's gescheiden tekenreeks:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

GEDAAN

Sinds PostgreSQL 9.0 het is nog eenvoudiger.


41
2017-07-06 12:46



Gebruik in SQL Server 2005 en hoger de onderstaande query om de rijen samen te voegen.

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

27
2017-10-12 00:16



Ik heb thuis geen toegang tot een SQL Server, dus ik vermoed de syntaxis hier, maar het is min of meer:

DECLARE @names VARCHAR(500)

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

23
2017-08-09 21:06