Jak połączyć tekst z wielu wierszy w pojedynczy ciąg tekstowy na serwerze SQL?


1910

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?


26
Aby uzyskać odpowiedzi specyficzne dla programu SQL Server, spróbuj tego pytania .
Matt Hamilton

17
W przypadku MySQL sprawdź Group_Concat z tej odpowiedzi
Pykler

26
Chciałbym, aby następna wersja programu SQL Server oferowała nową funkcję do eleganckiego rozwiązania łączenia wierszy z ciągami wielorzędowymi bez głupoty dla ścieżki XML.
Pete Alvin

4
Nie SQL, ale jeśli jest to jednorazowa czynność, możesz wkleić listę do tego narzędzia przeglądarki. Convert.town/column-to-comma-separated-list
Stack Man

3
W Oracle możesz użyć LISTAGG (COLUMN_NAME) z 11g r2, zanim pojawi się nieobsługiwana funkcja o nazwie WM_CONCAT (COLUMN_NAME), która robi to samo.
Richard

Odpowiedzi:


1431

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 PATHmetoda 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ć, substringaby 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

13
Świetne rozwiązanie. Poniższe informacje mogą być przydatne, jeśli chcesz obsługiwać znaki specjalne, takie jak te w HTML: Rob Farley: Obsługa znaków specjalnych za pomocą FOR XML PATH ('') .

10
Najwyraźniej to nie działa, jeśli nazwy zawierają znaki XML, takie jak <lub &. Zobacz komentarz @ BenHinman.
Sam

23
NB: Ta metoda opiera się na nieudokumentowanym zachowaniu 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.
Bacon Bits

26
@Whelkaholism Najważniejsze jest, FOR XMLaby generować XML, a nie łączyć dowolne ciągi. Dlatego ucieka &, <a >do kodów jednostki XML ( &amp;, &lt;, &gt;). Zakładam, że również będzie uciec "i 'do &quot;oraz &apos;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ę.
Boczek Bity

13
Dobra wiadomość: MS SQL Server będzie dodawać string_aggw v.Next. i wszystko to może zniknąć.
Jason C

1008

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):

  • Coalesce to naprawdę po prostu pomocny cheat, który osiąga dwie rzeczy:

1) Nie ma potrzeby inicjowania @Namesz pustą wartością ciągu.

2) Nie trzeba zdejmować dodatkowego separatora na końcu.

  • Powyższe rozwiązanie da niepoprawne wyniki, jeśli wiersz ma wartość NULL Name (jeśli istnieje NULL , NULL spowoduje @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).


72
Dla jasności, łączenie nie ma nic wspólnego z tworzeniem listy, tylko upewnia się, że wartości NULL nie są uwzględnione.
Graeme Perrow,

17
@Graeme Perrow Nie wyklucza wartości NULL (wymagane jest GDZIE - spowoduje to utratę wyników, jeśli jedna z wartości wejściowych ma wartość NULL), i jest wymagane w tym podejściu, ponieważ: NULL + nie-NULL -> NULL i inne niż NULL + NULL -> NULL; również @Name domyślnie ma wartość NULL, a w rzeczywistości ta właściwość służy tutaj jako domniemany wartownik w celu ustalenia, czy należy dodać „,”, czy nie.

62
Należy pamiętać, że ta metoda konkatenacji polega na wykonaniu zapytania przez SQL Server z określonym planem. Zostałem przyłapany na użyciu tej metody (z dodatkiem ORDER BY). Gdy miał do czynienia z małą liczbą wierszy, działał dobrze, ale przy większej ilości danych SQL Server wybrał inny plan, co spowodowało wybranie pierwszego elementu bez konkatenacji. Zobacz ten artykuł Anith Sen.
fbarber

17
Tej metody nie można użyć jako zapytania podrzędnego na liście wyboru lub klauzuli where, ponieważ używa ona zmiennej tSQL. W takich przypadkach można skorzystać z metod oferowanych przez @Ritesh
R. Schreurs

11
To nie jest niezawodna metoda konkatenacji. Jest nieobsługiwany i nie należy go używać (na Microsoft, np. Support.microsoft.com/en-us/kb/287515 , connect.microsoft.com/SQLServer/Feedback/Details/704389 ). Można to zmienić bez ostrzeżenia. Użyj techniki ŚCIEŻKA XML omówionej w stackoverflow.com/questions/5031204/ ... Napisałem więcej tutaj: marc.durdin.net/2015/07/…
Marc Durdin

453

SQL Server 2017+ i SQL Azure: STRING_AGG

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.

STRING_AGG (Transact-SQL)

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;

2
W przeciwieństwie do rozwiązań CLR masz kontrolę nad sortowaniem.
kanon

Wygląda na to, że w STRING_AGG istnieje ograniczenie wyświetlania 4000 znaków
Inspired przez

Czy istnieje sposób na sortowanie na wypadek, gdyby nie było GROUP BY (tak dla przykładu „Bez grupowania”)?
RuudvK

Aktualizacja: Udało mi się wykonać następujące czynności, ale czy istnieje czystszy sposób? WYBIERZ STRING_AGG (Nazwa, ',') AS DZIAŁY OD (WYBIERZ TOP 100000 Nazwa Z HumanResources.Department ORDER BY Name) D;
RuudvK

362

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

15
cholera, to jest niesamowite! Po uruchomieniu samodzielnie, tak jak w twoim przykładzie, wynik jest sformatowany jako hiperłącze, które po kliknięciu (w SSMS) otwiera nowe okno zawierające dane, ale gdy jest używane jako część większego zapytania, po prostu pojawia się jako ciąg. Czy to jest struna? czy jest to xml, który muszę traktować inaczej w aplikacji, która będzie korzystać z tych danych?
Ben

10
Podejście to również unika znaków XML, takich jak <i>. Zatem wybranie „<b>” + FName + „</b>” powoduje, że „& b & John & b & b & b; Paul ...”
Lukáš Lánský

8
Schludne rozwiązanie. Zauważam, że nawet jeśli nie dodam + ', ', nadal dodaje pojedynczą spację między każdym połączonym elementem.
Baodad

8
@Baodad To wydaje się być częścią umowy. Można obejść ten problem, zastępując dodaną postać tokena. Na przykład tworzy to idealną listę rozdzielaną przecinkami dla dowolnej długości:SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'')
NReilingh

1
Wow, właściwie w moich testach przy użyciu data () i zamiana jest DROGA bardziej wydajna niż nie. Super dziwne.
NReilingh

306

W SQL Server 2005

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

W SQL Server 2016

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


W SQL Server 2017 baza danych SQL Azure

Możesz użyć nowej funkcji STRING_AGG


3
Dobre wykorzystanie funkcji STUFF do łączenia dwóch wiodących znaków.
David,

3
Najbardziej podoba mi się to rozwiązanie, ponieważ mogę łatwo używać go na liście wyboru, dodając „as <label>”. Nie jestem pewien, jak to zrobić za pomocą rozwiązania @Ritesh.
R. Schreurs,

13
To jest lepsze niż Zaakceptowanych odpowiedź ponieważ opcja ta obsługuje również un-ucieczki XML znaki zastrzezone takie jak <, >, &, itd., Które FOR XML PATH('')będą automatycznie uciec.
BateTech,

To niesamowita odpowiedź, ponieważ rozwiązała problem i zapewnia najlepsze sposoby robienia rzeczy w różnych wersjach SQL, teraz chciałbym móc używać 2017 / Azure
Chris Ward

120

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

działa dobrze. Ale kiedy użyję SEPARATOR '", "', przegapię kilka znaków na końcu ostatniego wpisu. dlaczego to się może zdarzyć?
gooleem

@gooleem Nie jestem pewien, co masz na myśli, ale ta funkcja umieszcza tylko separator między elementami, a nie po. Jeśli to nie jest odpowiedź, polecam opublikowanie nowego pytania.
Darryl Hein

@DarrylHein dla moich potrzeb użyłem separatora jak wyżej. Ale to odcina mi kilka znaków na samym końcu wyjścia. To jest bardzo dziwne i wydaje się być błędem. Nie mam rozwiązania, właśnie obejrzałem.
gooleem,

Działa w zasadzie. Dwie rzeczy do rozważenia: 1) jeśli twoja kolumna nie jest 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_lenjak napisano w stackoverflow.com/a/1278210/1498405
hardmooth

58

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

2
To naprawdę najlepsze rozwiązanie IMO, ponieważ pozwala uniknąć problemów z kodowaniem, jakie przedstawia FOR XML. Użyłem Declare @Numbers AS Nvarchar(MAX)i działało dobrze. Czy możesz wyjaśnić, dlaczego nie zaleca się jego używania?
EvilDr,

7
To rozwiązanie zostało już opublikowane 8 lat temu! stackoverflow.com/a/194887/986862
Andre Figueiredo

Dlaczego to zapytanie zwraca ?? symbole zamiast cyrylicy? Czy to tylko problem z wydajnością?
Akmal Salikhov,

48

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 .


Jeśli potrzebujesz więcej niż jednej kolumny, na przykład identyfikator pracownika w nawiasach, użyj operatora konkatacji: select array_to_string(array_agg(name||'('||id||')'
Richard Fox

Nie dotyczy serwera sql , tylko mysql
GoldBishop

46

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.

Ostrzeżenie

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.


1
W starszych wersjach Oracle wm_concat jest idealny. Jego użycie jest wyjaśnione w linku prezent od Alexa. Dziękujemy Alex!
toscanelli,

LISTAGGdziała idealnie! Po prostu przeczytaj dokument podlinkowany tutaj. wm_concatusunięto z wersji 12c i wyższych.
asgs

34

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

2
Uważam, że to się nie udaje, gdy wartości zawierają symbole XML, takie jak <lub &.
Sam

28

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

11
Musisz zainicjować @names na coś innego niż null, w przeciwnym razie otrzymasz NULL; musisz także obsłużyć ogranicznik (w tym niepotrzebny)
Marc Gravell

3
jedynym problemem związanym z tym podejściem (z którego korzystam cały czas) jest to, że nie można go osadzić
ekkis,

1
Aby pozbyć się wiodącej przestrzeni, zmień zapytanie naSELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ' ' END + Name FROM Names
Tian van Heerden

Ponadto musisz sprawdzić, czy Nazwa nie jest pusta, możesz to zrobić, wykonując:SELECT @names = @names + ISNULL(' ' + Name, '')
Vita1ij,

28

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

1
Dla zaskoczony: wstawia zapytania 12 rzędów (a3 kolumny) do tymczasowego basetable, następnie tworzy rekurencyjne Expression tabelowym (rCTE), a następnie spłaszcza się name kolumnę do łańcucha oddzielony przecinkami na 4 grupy o ids. Na pierwszy rzut oka myślę, że to więcej pracy niż większość innych rozwiązań dla SQL Server.
knb 24.07.17

2
@knb: nie jestem pewien, czy to pochwała, potępienie czy po prostu niespodzianka. Podstawowa tabela jest taka, że ​​lubię, jak moje przykłady faktycznie działają, tak naprawdę nie ma to nic wspólnego z pytaniem.
jmoreno

26

Musisz stworzyć zmienną, która będzie utrzymywać twój końcowy wynik i wybrać do niego, tak jak to.

Najłatwiejsze rozwiązanie

DECLARE @char VARCHAR(MAX);

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

PRINT @char;

25

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


Aby to zrobić z kolumnami, które nie są tekstowe, musisz dodać rzutowanie typu:SELECT string_agg(non_text_type::text, ',') FROM table
Torben Kohlmeier

@TorbenKohlmeier: potrzebujesz tego tylko w przypadku kolumn niebędących znakami (np. Liczba całkowita, dziesiętna). Działa dobrze dla varcharlubchar
a_horse_w_no_name


18

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(''))
         , ' ', ', ') 

2
To najlepsza odpowiedź tutaj w mojej opinii. Użycie zmiennej deklaruj nie jest dobre, gdy trzeba dołączyć do innej tabeli, a to jest miłe i krótkie. Dobra robota.
David Roussel,

7
to nie działa dobrze, jeśli dane FName mają już spacje, na przykład „My Name”
binball

Naprawdę działa dla mnie na ms-sql 2016 Wybierz REPLACE ((wybierz Name AS 'data ()' z Brand Where Id IN (1,2,3,4) dla ścieżki xml ('')), '', ' , ') as allBrands
Rejwanul Reja

17

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.)


Przed użyciem przecinka jest spacja :(
slayernoah,

1
Wystarczy wymienić ', 'ze ','jeśli nie chcesz dodatkową przestrzeń.
Daniel Reis,

13
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

10
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 :-)


10

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 

7

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ć.


7

Aby uniknąć wartości pustych, możesz użyć CONCAT ()

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

Byłoby miło wiedzieć, dlaczego działa CONCAT. Link do MSDN byłby miły.
Reversed Engineer

7

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)

Jeśli usuwasz dwa ostatnie symbole „”, musisz dodać „,” po nazwie („SELECT \ @names = \ @names + Name +”, „FROM Names”). W ten sposób ostatnie dwa znaki zawsze będą oznaczać „,”.
JT_

W moim przypadku musiałem pozbyć się wiodącego przecinka, więc zmień zapytanie, aby SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ', ' END + Name FROM Namesnie trzeba było go później obcinać.
Tian van Heerden

6

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!!!


1
Łącze DLL to błąd 404. Używanie do tego zestawu jest przesadą. Zobacz najlepszą odpowiedź dla programu SQL Server.
Protiguous

6

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

5

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

5

Jeśli chcesz poradzić sobie z zerami, możesz to zrobić, dodając klauzulę where lub dodając kolejną wartość COALESCE wokół pierwszej.

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

5

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

Z dokumentacji MySql



4

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

5
Niestety takie zachowanie nie jest oficjalnie wspierane. MSDN mówi: „Jeśli do listy wyboru odwołuje się zmienna, należy jej przypisać wartość skalarną lub instrukcja SELECT powinna zwracać tylko jeden wiersz”. I są ludzie, którzy zauważyli problemy: sqlmag.com/sql-server/multi-row-variable-assignment-and-order
blueling
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.