Overweeg een databasetabel met namen, met drie rijen:
Peter
Paul
Mary
Is er een gemakkelijke manier om dit om te zetten in een enkele reeks van Peter, Paul, Mary?
Antwoord 1, autoriteit 100%
Als u SQL Server 2017 of Azure gebruikt, raadpleegt u Mathieu Renda-antwoord.
Ik had een soortgelijk probleem toen ik probeerde twee tabellen samen te voegen met een-op-veel-relaties. In SQL 2005 ontdekte ik dat de XML PATH-methode de aaneenschakeling van de rijen heel gemakkelijk aankan.
Als er een tabel is met de naam STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Het resultaat dat ik verwachtte was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
Ik heb de volgende T-SQLgebruikt:
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]
Je kunt hetzelfde op een compactere manier doen als je de komma’s aan het begin kunt samenvoegen en substringkunt gebruiken om de eerste over te slaan, zodat je geen subquery hoeft te doen :
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
Antwoord 2, autoriteit 69%
Dit antwoord kan onverwachte resultaten opleverenGebruik voor consistente resultaten een van de FOR XML PATH-methoden die in andere antwoorden worden beschreven.
Gebruik COALESCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Gewoon wat uitleg (aangezien dit antwoord relatief regelmatig bekeken lijkt te worden):
- Coalesce is eigenlijk gewoon een handige cheat die twee dingen bereikt:
1) Het is niet nodig om @Nameste initialiseren met een lege tekenreekswaarde.
2) U hoeft aan het einde geen extra scheidingsteken te verwijderen.
- De bovenstaande oplossing geeft onjuiste resultaten als een rij een NULLNaamwaarde heeft (als er een NULLis, maakt de NULL
@NamesNULLna die rij, en de volgende rij begint opnieuw als een lege tekenreeks. Gemakkelijk te verhelpen 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 gewoon NULLs uit, de tweede optie houdt ze in de lijst met een markeringsbericht [vervang ‘N.v.t.’ door wat geschikt is voor jij]).
Antwoord 3, autoriteit 40%
SQL Server 2017+ en SQL Azure: STRING_AGG
Vanaf de volgende versie van SQL Server kunnen we eindelijk rijen aaneenschakelen zonder toevlucht te hoeven nemen tot variabelen of XML-hekserij.
Zonder 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 subsorteren
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Antwoord 4, autoriteit 25%
Een methode die nog niet wordt weergegeven via de opdracht XMLdata()in MS SQL Server is:
Actieve tabel genaamd Namelist met één kolom genaamd FNAME,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
Retouren:
"Peter, Paul, Mary, "
Alleen de extra komma moet worden afgehandeld.
EDIT: Zoals geadopteerd van @ Nreilingh’s commentaar, kunt u de volgende methode gebruiken om de trailing Comma 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
Antwoord 5, Autoriteit 21%
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
U kunt de gebruiken Voor JSON SYNTAX
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 wordt
Id Emails
1 [email protected]
2 NULL
3 [email protected], [email protected]
Hiermee werkt u zelfs uw gegevens bevat ongeldige XML-tekens
de '"},{"_":"'is veilig, want als uw gegevens '"},{"_":"',bevatten worden escaped naar "},{\"_\":\"
Je kunt ', 'vervangen door elk tekenreeksscheidingsteken
En in SQL Server 2017, Azure SQL Database
Je kunt de nieuwe STRING_AGG gebruiken functie
Antwoord 6, autoriteit 9%
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
Antwoord 7, autoriteit 4%
Gebruik COALESCE– Lees hier meer
Voor een voorbeeld:
102
103
104
Schrijf dan onderstaande code op 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
Antwoord 8, autoriteit 3%
Postgres-arrays zijn geweldig. Voorbeeld:
Maak enkele 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)
KLAAR
Sinds PostgreSQL 9.0 is het nog eenvoudiger.
Antwoord 9, autoriteit 3%
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 kans bestaat dat de resulterende string meer dan 4000 tekens bevat. Het zal een uitzondering gooien. Als dat het geval is, moet u ofwel de uitzondering afhandelen of uw eigen functie gebruiken die voorkomt dat de samengevoegde tekenreeks meer dan 4000 tekens bevat.
Antwoord 10, autoriteit 2%
Gebruik in SQL Server 2005 en later 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
Antwoord 11, autoriteit 2%
Ik heb thuis geen toegang tot een SQL Server, dus ik gok op de syntaxis hier, maar het is min of meer:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
Antwoord 12, autoriteit 2%
Er werd een recursieve CTE-oplossing voorgesteld, maar er werd geen code verstrekt. De onderstaande code is een voorbeeld van een recursieve CTE. Merk op dat hoewel de resultaten overeenkomen met de vraag, de gegevens niet heelovereenkomen met de gegeven beschrijving, omdat ik aanneem dat je dit echt wilt doen op groepen rijen, niet op alle rijen in de tabel. Het wijzigen zodat het overeenkomt met alle rijen in de tabel is een oefening voor de lezer.
;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
Antwoord 13, Autoriteit 2%
U moet een variabele maken die uw eindresultaat bevat en erin selecteert, zoals SO.
gemakkelijkste oplossing
DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + [column], [column])
FROM [table];
PRINT @char;
Antwoord 14
In SQL Server Vnext Dit wordt ingebouwd met de functie String_agg, lees er meer over hier:
https://msdn.microsoft.com/en-us/library/mt790580.aspx
Antwoord 15
Met XML heeft me geholpen bij het krijgen van rijen gescheiden met komma’s. Voor de extra komma kunnen we de vervangingsfunctie van SQL & NBSP; server gebruiken. In plaats van een komma toe te voegen, zal het gebruik van de ‘data ()’ de rijen met spaties samenvoegen, die later kunnen worden vervangen door komma’s als de onderstaande syntaxis.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
Antwoord 16
een kant-en-klare oplossing, zonder extra komma’s:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
Een lege lijst resulteert in een NULL-waarde.
Gewoonlijk voegt u de lijst toe aan een tabelkolom of programmavariabele: pas de maximale lengte van 255 aan uw behoefte aan.
(Diwakar en Jens Frandsen hebben goede antwoorden gegeven, maar moeten worden verbeterd.)
Antwoord 17
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')
Hier is een voorbeeld:
DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
Antwoord 18
Dit werkte voor mij (SqlServer 2016):
SELECT CarNamesString = STUFF((
SELECT ',' + [Name]
FROM tbl_cars
FOR XML PATH('')
), 1, 1, '')
Hier is de bron:
https://www.mytecbits.com/
En een oplossing voor MySql(aangezien deze pagina wordt weergegeven in Google voor MySql)
SELECT [Name],
GROUP_CONCAT(DISTINCT [Name] SEPARATOR ',')
FROM tbl_cars
Antwoord 19
Met de andere antwoorden moet de persoon die het antwoord leest op de hoogte is van een specifieke domeinstabel zoals voertuig of student. De tabel moet worden gemaakt en ingevuld met gegevens om een oplossing te testen.
Hieronder is een voorbeeld dat SQL Server “InformationSchema.columns” -tabel gebruikt. Door deze oplossing te gebruiken, moeten geen tabellen worden gemaakt of gegevens toegevoegd. Dit voorbeeld maakt een door komma’s gescheiden lijst van kolomnamen voor alle tabellen in de database.
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
Antwoord 20
In MS SQL Server 2017 of latere versies kunt u string_agg () -functie gebruiken om komma-gescheiden waarden te genereren. onder een voorbeeld.
SELECT
VendorId,STRING_AGG(FirstName,',') UsersName FROM
Users
where VendorId!=9 GROUP BY VendorId
Antwoord 21
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
Hiermee wordt de zwerfcomma aan het begin.
Als u echter andere kolommen nodig heeft, of naar CSV een kindertafel, moet u dit in een gesorteerd veld van een scalaire gebruiker (UDF) wikkelen.
Je kunt het XML-pad ook gebruiken als een gecorreleerde subquery in de SELECT-clausule (maar ik zou moeten wachten tot ik weer aan het werk ga omdat Google thuis geen werk doet 🙂
Antwoord 22
Voor Oracle DB’s, zie deze vraag: Hoe kunnen meerdere rijen worden samengevoegd tot één in Oracle zonder een opgeslagen procedure te maken?
Het beste antwoord lijkt te zijn van @Emmanuel, met behulp van de ingebouwde LISTAGG()-functie, beschikbaar in Oracle 11g Release 2 en later.
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id
zoals @user762952 opmerkte, en volgens de documentatie van Oracle http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php, is de WM_CONCAT()-functie ook een optie. Het lijkt stabiel, maar Oracle raadt het expliciet af om het te gebruiken voor SQL-applicaties, dus gebruik op eigen risico.
Anders dan dat, zul je je eigen functie moeten schrijven; het Oracle-document hierboven heeft een handleiding over hoe dat te doen.
Antwoord 23
Om null-waarden te vermijden, kunt u CONCAT() gebruiken
DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name)
FROM Names
select @names
Antwoord 24
MySQL compleet Voorbeeld:
We hebben gebruikers die veel gegevens kunnen hebben en we willen een uitvoer hebben, waar we alle gebruikersgegevens in een lijst kunnen zien:
Resultaat:
___________________________
| id | rowList |
|-------------------------|
| 0 | 6, 9 |
| 1 | 1,2,3,4,5,7,8,1 |
|_________________________|
Tabelconfiguratie:
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);
Vraag:
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
Antwoord 25
Ik hield echt van de elegantie van Dana’s antwoord. Ik wilde het gewoon compleet maken.
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
Antwoord 26
Dit antwoord vereist enige privilege op de server om te werken.
Assemblageszijn een goede optie voor u. Er zijn veel sites die uitleggen hoe je het kunt maken. Degene die volgens mij heel goed is uitgelegd, is deze een
Als je wilt, heb ik de assembly al gemaakt en het is mogelijk om de DLL hier.
Als je het eenmaal hebt gedownload, moet je het volgende script in je SQL Server uitvoeren:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;
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
Houd er rekening mee dat het pad naar de assembly mogelijk toegankelijk is voor de server. Omdat je alle stappen met succes hebt uitgevoerd, kun je de functie gebruiken zoals:
SELECT dbo.Concat(field1, ',')
FROM Table1
Hopelijk helpt het!!!
UPDATE:
Sinds MS-SQL 2017 is het mogelijk om de STRING_AGG functie
Antwoord 27
Ik gebruik select meestal op deze manier om strings in SQL Server samen te voegen:
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
Antwoord 28
Als u met nulls wilt omgaan, kunt u dit doen door een waar-clausule toe te voegen of een andere COALESCE rond de eerste toe te voegen.
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
Antwoord 29
bovenop het antwoord van Chris Shaffer
als uw gegevens herhaald kunnen worden, zoals
Tom
Ali
John
Ali
Tom
Mike
In plaats van Tom,Ali,John,Ali,Tom,Mike
Je kunt DISTINCT gebruiken om duplicaten te voorkomen en Tom,Ali,John,Mike
te krijgen
DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names
Antwoord 30
In Oracle is dit wm_concat. Ik geloof dat deze functie beschikbaar is in de 10g-releaseen hoger.