Ia în considerare un tabel de bază de date care deține nume, cu trei rânduri:
Peter
Paul
Mary
Există o modalitate ușoară de a transforma acest lucru într-un singur șir de Petru, Pavel, Mary?
Dacă sunteți pe SQL Server 2017 sau Azure, a se vedea Mathieu Renda raspuns.
Am avut o problemă similară când am fost încercarea de a alătura două tabele cu una-la-mai multe relații. În SQL 2005 am constatat că XML CALEA
metoda poate ocupa de concatenare de șiruri foarte usor.
Dacă există un tabel numit STUDENȚILOR
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Rezultatul m-am așteptat a fost:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
Am folosit următoarele 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]
Puteți face același lucru într-un mod mult mai compact dacă puteți concat virgulele de la începutul și de a folosi `subșir, pentru a sări peste prima, astfel încât să don't nevoie pentru a face o sub-interogare:
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
Acest răspuns poate reveni rezultate neașteptate Pentru rezultate consistente, utilizați una dintre PENTRU XML CALEA metodele detaliate în alte răspunsuri.
Utilizarea COAGULEZE
:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Doar o explicatie (din acest răspuns pare să se relativ regulat de vedere):
Nu este nevoie pentru a inițializa `@Nume cu o valoare șir gol.
Nu trebuie să dezbrace un plus separator la sfârșitul anului.
@Nume
NUL după aceea rând, și rândul următor va începe peste ca un șir gol din nou. Ușor de fixat cu una din cele două soluții:DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
sau:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
În funcție de ce comportamentul dorit (prima opțiune doar filtre NULLs, cea de-a doua opțiune păstrează-le în listă cu un marker mesaj [a se înlocui 'N/O' cu tot ce este necesar pentru tine]).
Incepand cu urmatoarea versiune de SQL Server, în sfârșit, putem înlănțui pe rânduri fără a fi nevoie să recurgă la orice variabilă sau XML vrăjitorie.
Fără grupare
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
Cu grupare :
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Cu gruparea și sub-sortare
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
O metodă nu a demonstrat încă prin anii XML
date()
comanda in MS SQL Server este:
Presupunem tabel numit NameList cu o singură coloană numită FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
se întoarce:
"Peter, Paul, Mary, "
Numai virgulă în plus trebuie să fie tratate cu.
Edit: Cum a fost adoptat de la @NReilingh's comentariu, puteți folosi următoarea metodă pentru a elimina trailing comma. Presupunând aceeași masă și coloană nume:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
puteți utiliza PENTRU sintaxa JSON
de exemplu
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
Iar rezultatul va deveni
Id Emails
1 [email protected]
2 NULL
3 [email protected], [email protected]
Acest lucru va funcționa chiar datele dumneavoastră conține caractere nevalide XML
a '"},{"_":"'
este în siguranță pentru că dacă datele conțin '"},{"_":"',
va fi evadat de la "},{\"_\":\"
Puteți înlocui', '
cu orice șir de caractere separator
Puteți utiliza noul STRING_AGG funcția
În MySQL există o funcție, GROUP_CONCAT(), care vă permite pentru a concatena valorile din mai multe rânduri. Exemplu:
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
Utilizare COAGULEZE - Afla mai multe de aici
De exemplu:
102
103
104
Apoi scrie codul de mai jos în 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
Rezultatul ar fi:
102,103,104
Postgres matrice sunt minunat. Exemplu:
Crea niște date de test:
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)
De a le agrega într-o matrice:
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Converti matrice la un șir delimitat de virgule:
test=# select array_to_string(array_agg(name), ', ') from names;
array_to_string
-------------------
Peter, Paul, Mary
(1 row)
FĂCUT
Deoarece PostgreSQL 9.0 este chiar mai ușor.
Oracle 11g Release 2 sprijină LISTAGG funcție. Documentare aici.
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.
Fii atent de punere în aplicare această funcție dacă există posibilitatea de șirul rezultat peste 4000 de caractere. Se va arunca o excepție. Dacă asta's cazul, atunci ai nevoie pentru a gestiona o excepție sau rola propria funcție care împiedică s-a alăturat șirului de peste 4000 de caractere.
În SQL Server 2005 și mai târziu, utilizați interogarea de mai jos pentru a înlănțui rânduri.
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
Un recursiv CTE soluție a fost sugerat, dar nici un cod a fost furnizat. Codul de mai jos este un exemplu de un recursiv CTE. Rețineți că, deși rezultatele meci întrebarea, datele nu't destul se potrivesc descrierii, așa cum presupun că vrei cu adevărat să faci acest lucru pe grupuri de rânduri, nu toate rândurile din tabel. Schimba-l pentru a se potrivi toate rândurile din tabel este lăsată ca exercițiu pentru cititor.
;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
Aveți nevoie pentru a crea o variabilă care va stoca rezultatul final și selectați în ea, așa.
DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + [column], [column])
FROM [table];
PRINT @char;
În SQL Server vNext acest lucru va fi construit în cu STRING_AGG funcție, citi mai multe despre asta aici: https://msdn.microsoft.com/en-us/library/mt790580.aspx
Folosind XML m-a ajutat în obținerea de rânduri separate cu virgule. Pentru virgulă în plus putem folosi funcția de a înlocui de SQL Server. În loc de a adăuga o virgulă, uz de CA 'date()' va înlănțui rânduri cu spații, care mai târziu pot fi înlocuite cu virgule ca sintaxa scris mai jos.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
Un gata-la-utilizare soluție, cu nici o suplimentare de virgule:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
O listă goală va duce în valoare NULĂ. De obicei, vă va introduce în listă într-o coloană de tabel sau variabilă program: reglați 255 lungime max de nevoie de dumneavoastră.
(Diwakar și Jens Frandsen oferit răspunsuri bune, dar au nevoie de îmbunătățire.)
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')
Aici's o mostră:
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
Cu alte răspunsuri, persoana a citit răspunsul trebuie să fie conștienți de un anumit domeniu de masă, cum ar fi vehicule sau de student. Tabelul trebuie să fie creat și populat cu date pentru a testa o soluție.
Mai jos este un exemplu care utilizează SQL Server "Information_Schema.Coloane" masa. Folosind această soluție, nu tabele trebuie să fie create sau datele adăugate. Acest exemplu creează o listă separată prin virgule de nume de coloană pentru toate tabelele din baza de date.
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
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
Acest lucru pune rătăcit prin virgulă de la început.
Cu toate acestea, dacă aveți nevoie de alte coloane, sau pentru a CSV-un tabel copil trebuie să încheie acest lucru într-un scalar definit de utilizator câmp (UDF).
Puteți utiliza XML calea ca o subinterogare corelată în clauza SELECT prea (dar nu'd trebuie să aștepte până mă întorc la muncă pentru că Google nu't face chestii de lucru la domiciliu :-)