Rozważmy tabelę bazy danych zawierającą nazwy z trzema wierszami:
Peter
Paul
Mary
Czy istnieje prosty sposób, aby zmienić to w pojedynczy ciąg Peter, Paul, Mary
?
Rozważmy tabelę bazy danych zawierającą nazwy z trzema wierszami:
Peter
Paul
Mary
Czy istnieje prosty sposób, aby zmienić to w pojedynczy ciąg Peter, Paul, Mary
?
Odpowiedzi:
Jeśli korzystasz z SQL Server 2017 lub Azure, zobacz odpowiedź Mathieu Renda .
Miałem podobny problem, kiedy próbowałem połączyć dwie tabele z relacjami jeden do wielu. W SQL 2005 odkryłem, że ta XML PATH
metoda bardzo łatwo radzi sobie z konkatenacją wierszy.
Jeśli istnieje tabela o nazwie STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Spodziewałem się wyniku:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
Użyłem następujących 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]
Możesz zrobić to samo w bardziej zwarty sposób, jeśli możesz przeciąć przecinki na początku i użyć, substring
aby pominąć pierwszy, aby nie trzeba było wykonywać zapytania podrzędnego:
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
<
lub &
. Zobacz komentarz @ BenHinman.
FOR XML PATH ('')
. Oznacza to, że nie należy jej uważać za niezawodną, ponieważ jakakolwiek łatka lub aktualizacja może zmienić sposób jej działania. Zasadniczo polega na przestarzałej funkcji.
FOR XML
aby generować XML, a nie łączyć dowolne ciągi. Dlatego ucieka &
, <
a >
do kodów jednostki XML ( &
, <
, >
). Zakładam, że również będzie uciec "
i '
do "
oraz '
w atrybutach, jak również. To nie GROUP_CONCAT()
, string_agg()
, array_agg()
, listagg()
, itd., Nawet jeśli można zrobić to niby zrobić. My powinniśmy spędzać nasz czas wymagający Microsoft wdrożyć właściwą funkcję.
string_agg
w v.Next. i wszystko to może zniknąć.
Ta odpowiedź może zwrócić nieoczekiwane wyniki Aby uzyskać spójne wyniki, użyj jednej z metod FOR XML PATH opisanych w innych odpowiedziach.
Użyj COALESCE
:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Tylko wyjaśnienie (ponieważ ta odpowiedź wydaje się mieć stosunkowo regularne wyświetlenia):
1) Nie ma potrzeby inicjowania @Names
z pustą wartością ciągu.
2) Nie trzeba zdejmować dodatkowego separatora na końcu.
@Names
NULL po tym wierszu, a następny wiersz zacznie od nowa jako pusty ciąg znaków. Łatwo naprawiony za pomocą jednego z dwóch rozwiązania:DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
lub:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
W zależności od tego, jakie zachowanie chcesz (pierwsza opcja po prostu odfiltrowuje wartości NULL , druga opcja utrzymuje je na liście z komunikatem znacznika [zamień „nie dotyczy” na to, co jest odpowiednie dla ciebie).
Począwszy od kolejnej wersji programu SQL Server, możemy w końcu łączyć wiersze bez konieczności uciekania się do jakiejkolwiek zmiennej lub czarów XML.
Bez grupowania
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
Z grupowaniem:
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Z grupowaniem i sortowaniem
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Jedną z metod, których jeszcze nie pokazano za pomocą XML
data()
polecenia w MS SQL Server, jest:
Załóżmy tabelę o nazwie NameList z jedną kolumną o nazwie FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
zwroty:
"Peter, Paul, Mary, "
Należy uwzględnić tylko dodatkowy przecinek.
Edycja: Zgodnie z komentarzem @ NReilingh możesz użyć następującej metody, aby usunąć przecinek końcowy. Zakładając tę samą nazwę tabeli i kolumny:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
+ ', '
, nadal dodaje pojedynczą spację między każdym połączonym elementem.
SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'')
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
możesz użyć składni FOR JSON
to znaczy
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
I wynik się stanie
Id Emails
1 abc@gmail.com
2 NULL
3 def@gmail.com, xyz@gmail.com
Działa to nawet twoje dane zawierają nieprawidłowe znaki XML
'"},{"_":"'
jest bezpieczne, ponieważ jeśli wy zawierać dane'"},{"_":"',
będzie uciekł"},{\"_\":\"
Możesz zastąpić ', '
dowolnym separatorem ciągów
Możesz użyć nowej funkcji STRING_AGG
<
, >
, &
, itd., Które FOR XML PATH('')
będą automatycznie uciec.
W MySQL znajduje się funkcja GROUP_CONCAT () , która pozwala łączyć wartości z wielu wierszy. Przykład:
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
SEPARATOR '", "'
, przegapię kilka znaków na końcu ostatniego wpisu. dlaczego to się może zdarzyć?
CHAR
, musisz rzucić ją, np. Przez GROUP_CONCAT( CAST(id AS CHAR(8)) ORDER BY id ASC SEPARATOR ',')
2), jeśli masz wiele wartości, powinieneś zwiększyć group_concat_max_len
jak napisano w stackoverflow.com/a/1278210/1498405
Użyj COALESCE - dowiedz się więcej tutaj
Dla przykładu:
102
103
104
Następnie wpisz poniższy kod na serwerze SQL,
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
Dane wyjściowe byłyby:
102,103,104
Declare @Numbers AS Nvarchar(MAX)
i działało dobrze. Czy możesz wyjaśnić, dlaczego nie zaleca się jego używania?
Tablice Postgres są niesamowite. Przykład:
Utwórz dane testowe:
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)
Agreguj je w tablicy:
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Konwertuj tablicę na ciąg rozdzielany przecinkami:
test=# select array_to_string(array_agg(name), ', ') from names;
array_to_string
-------------------
Peter, Paul, Mary
(1 row)
GOTOWY
Od PostgreSQL 9.0 jest jeszcze łatwiejszy .
select array_to_string(array_agg(name||'('||id||')'
Oracle 11g Release 2 obsługuje funkcję LISTAGG. Dokumentacja tutaj .
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.
Zachowaj ostrożność przy wdrażaniu tej funkcji, jeśli istnieje możliwość, że wynikowy ciąg przekroczy ponad 4000 znaków. Wyrzuci wyjątek. W takim przypadku musisz obsłużyć wyjątek lub uruchomić własną funkcję, która zapobiega łączeniu ciągu przekraczającego 4000 znaków.
LISTAGG
działa idealnie! Po prostu przeczytaj dokument podlinkowany tutaj. wm_concat
usunięto z wersji 12c i wyższych.
W SQL Server 2005 i nowszych wersjach użyj poniższego zapytania, aby połączyć wiersze.
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
<
lub &
.
Nie mam dostępu do SQL Server w domu, więc zgaduję tutaj o składni, ale mniej więcej:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ' ' END + Name FROM Names
SELECT @names = @names + ISNULL(' ' + Name, '')
Zaproponowano rekurencyjne rozwiązanie CTE, ale nie podano kodu. Poniższy kod jest przykładem rekurencyjnego CTE. Zauważ, że chociaż wyniki pasują do pytania, dane nie do końca pasują do podanego opisu, ponieważ zakładam, że naprawdę chcesz to robić w grupach wierszy, a nie we wszystkich wierszach w tabeli. Zmiana go tak, aby pasowała do wszystkich wierszy w tabeli, pozostaje dla czytelnika ćwiczeniem.
;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
name
kolumnę do łańcucha oddzielony przecinkami na 4 grupy o id
s. Na pierwszy rzut oka myślę, że to więcej pracy niż większość innych rozwiązań dla SQL Server.
Począwszy od PostgreSQL 9.0 jest to dość proste:
select string_agg(name, ',')
from names;
W wersjach wcześniejszych niż 9.0 array_agg()
można używać, jak pokazuje hgmnz
SELECT string_agg(non_text_type::text, ',') FROM table
varchar
lubchar
W SQL Server vNext będzie to wbudowane w funkcję STRING_AGG, czytaj więcej na ten temat tutaj: https://msdn.microsoft.com/en-us/library/mt790580.aspx
Korzystanie z XML pomogło mi w oddzieleniu wierszy przecinkami. Za dodatkowy przecinek możemy użyć funkcji zamiany programu SQL Server. Zamiast dodawać przecinek, użycie AS 'data ()' połączy wiersze ze spacjami, które później można zastąpić przecinkami zgodnie ze składnią zapisaną poniżej.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
Gotowe do użycia rozwiązanie bez dodatkowych przecinków:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
Pusta lista spowoduje wartość NULL. Zazwyczaj wstawiasz listę do kolumny tabeli lub zmiennej programu: dostosuj maksymalną długość 255 do swoich potrzeb.
(Diwakar i Jens Frandsen udzielili dobrych odpowiedzi, ale wymagają poprawy.)
', '
ze ','
jeśli nie chcesz dodatkową przestrzeń.
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')
Oto próbka:
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
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
To stawia bezpański przecinek na początku.
Jeśli jednak potrzebujesz innych kolumn lub CSV tabeli podrzędnej, musisz zawinąć ją w skalarne pole zdefiniowane przez użytkownika (UDF).
Możesz użyć ścieżki XML jako skorelowanego podkwerendy również w klauzuli SELECT (ale musiałbym poczekać, aż wrócę do pracy, ponieważ Google nie wykonuje pracy w domu :-)
Przy innych odpowiedziach osoba czytająca odpowiedź musi być świadoma konkretnej tabeli domen, takiej jak pojazd lub uczeń. Tabela musi zostać utworzona i wypełniona danymi, aby przetestować rozwiązanie.
Poniżej znajduje się przykład wykorzystujący tabelę „Information_Schema.Columns” programu SQL Server. Korzystając z tego rozwiązania, nie trzeba tworzyć tabel ani dodawać danych. Ten przykład tworzy listę nazw kolumn oddzieloną przecinkami dla wszystkich tabel w bazie danych.
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
W przypadku baz danych Oracle zobacz to pytanie: Jak można połączyć wiele wierszy w jeden w Oracle bez tworzenia procedury składowanej?
Najlepszą odpowiedzią wydaje się być @Emmanuel, wykorzystujący wbudowaną funkcję LISTAGG (), dostępną w Oracle 11g Release 2 i późniejszych.
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id
jak wskazał @ user762952 i zgodnie z dokumentacją Oracle http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php , funkcja WM_CONCAT () jest również opcją. Wydaje się stabilny, ale Oracle wyraźnie odradza używanie go do dowolnej aplikacji SQL, więc używaj na własne ryzyko.
Poza tym będziesz musiał napisać własną funkcję; powyższy dokument Oracle zawiera przewodnik, jak to zrobić.
Aby uniknąć wartości pustych, możesz użyć CONCAT ()
DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name)
FROM Names
select @names
Naprawdę podobała mi się elegancja odpowiedzi Dany . Chciałem tylko, żeby był kompletny.
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ', ' END + Name FROM Names
nie trzeba było go później obcinać.
Ta odpowiedź wymaga pewnych uprawnień na serwerze do pracy.
Zespoły są dla Ciebie dobrą opcją. Istnieje wiele witryn, które wyjaśniają, jak je utworzyć. Ten, moim zdaniem jest bardzo dobrze wyjaśnione jest to jeden
Jeśli chcesz, utworzyłem już zespół i możliwe jest pobranie biblioteki DLL tutaj .
Po pobraniu konieczne będzie uruchomienie następującego skryptu na serwerze SQL:
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
Zauważ, że ścieżka do zestawu może być dostępna dla serwera. Ponieważ pomyślnie wykonałeś wszystkie kroki, możesz użyć funkcji takiej jak:
SELECT dbo.Concat(field1, ',')
FROM Table1
Mam nadzieję, że to pomoże!!!
Kompletny MySQL Przykład:
Mamy użytkowników, którzy mogą mieć wiele danych i chcemy mieć dane wyjściowe, w których możemy zobaczyć dane wszystkich użytkowników na liście:
Wynik:
___________________________
| id | rowList |
|-------------------------|
| 0 | 6, 9 |
| 1 | 1,2,3,4,5,7,8,1 |
|_________________________|
Konfiguracja stołu:
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);
Pytanie:
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
Zazwyczaj używam takiego wyboru do łączenia łańcuchów w SQL Server:
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
To zadziałało dla mnie ( SqlServer 2016 ):
SELECT CarNamesString = STUFF((
SELECT ',' + [Name]
FROM tbl_cars
FOR XML PATH('')
), 1, 1, '')
Oto źródło: https://www.mytecbits.com/
I rozwiązanie dla MySql (ponieważ ta strona pojawia się w Google dla MySql)
SELECT [Name],
GROUP_CONCAT(DISTINCT [Name] SEPARATOR ',')
FROM tbl_cars
Tak jest w Oracle wm_concat
. Wierzę, że ta funkcja jest dostępna w wersji 10 g i wyższej.
To też może być przydatne
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
zwroty
Peter,Paul,Mary