Câu hỏi Làm thế nào để nối văn bản từ nhiều hàng vào một chuỗi văn bản duy nhất trong máy chủ SQL?


Hãy xem xét một bảng cơ sở dữ liệu đang nắm giữ tên, với ba hàng:

Peter
Paul
Mary

Có cách nào dễ dàng để biến điều này thành một chuỗi Peter, Paul, Mary?


1489
2017-10-11 23:49


gốc


Để có câu trả lời cụ thể cho SQL Server, hãy thử câu hỏi này. - Matt Hamilton
Đối với MySQL, hãy xem Group_Concat từ câu trả lời này - Pykler
Tôi muốn các phiên bản tiếp theo của SQL Server sẽ cung cấp một tính năng mới để giải quyết concatination chuỗi nhiều hàng thanh lịch mà không có sự silliness của FOR XML PATH. - Pete Alvin
hướng dẫn từng bước để mô tả các câu trả lời ở trên: hãy thử bài viết này: [ sqlmatters.com/Articles/… ] - saber tabatabaee yazdi
Không phải SQL, nhưng nếu đây là một điều duy nhất, bạn có thể dán danh sách vào công cụ trong trình duyệt này convert.town/column-to-comma-separated-list - Stack Man


Các câu trả lời:


Nếu bạn đang sử dụng SQL Server 2017 hoặc Azure, hãy xem Mathieu Renda trả lời.

Tôi đã có một vấn đề tương tự khi tôi đã cố gắng để tham gia hai bảng với một-nhiều mối quan hệ. Trong SQL 2005 tôi thấy rằng XML PATH phương pháp có thể xử lý việc nối các hàng rất dễ dàng.

Nếu có một bảng gọi là STUDENTS

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

Kết quả tôi mong đợi là:

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

Tôi đã sử dụng các mục sau 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]

Bạn có thể làm điều tương tự theo một cách nhỏ gọn hơn nếu bạn có thể concat dấu phẩy lúc bắt đầu và sử dụng substring để bỏ qua phần đầu tiên, do đó bạn không cần thực hiện truy vấn phụ:

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

1110
2018-02-13 11:53



Giải pháp tuyệt vời. Sau đây có thể hữu ích nếu bạn cần xử lý các ký tự đặc biệt như các ký tự trong HTML: Rob Farley: Xử lý các ký tự đặc biệt với FOR XML PATH ('').
Rõ ràng điều này không hoạt động nếu các tên chứa các ký tự XML như < hoặc là &. Xem nhận xét của @ BenHinman. - Sam
NB: Phương pháp này phụ thuộc vào hành vi không có giấy tờ của FOR XML PATH (''). Điều đó có nghĩa là nó không nên được coi là đáng tin cậy như bất kỳ bản vá hoặc cập nhật có thể thay đổi cách chức năng này. Nó cơ bản dựa vào một tính năng không được chấp nhận. - Bacon Bits
@Whelkaholism Điểm mấu chốt là FOR XML được thiết kế để tạo ra XML, không nối các chuỗi tùy ý. Đó là lý do tại sao nó thoát &, < và > sang mã thực thể XML (&amp;, &lt;, &gt;). Tôi cho rằng nó cũng sẽ thoát " và ' đến &quot; và &apos; trong các thuộc tính. nó là không phải  GROUP_CONCAT(), string_agg(), array_agg(), listagg(), vv ngay cả khi bạn có thể làm cho nó làm điều đó. Chúng tôi Nên được dành thời gian của chúng tôi yêu cầu Microsoft thực hiện một chức năng thích hợp. - Bacon Bits
Tin tốt: MS SQL Server sẽ được thêm string_agg trong v.Next. và tất cả điều này có thể biến mất. - Jason C


Câu trả lời này có thể trả lại kết quả không mong muốn khi có mệnh đề ORDER BY. Để có kết quả nhất quán, hãy sử dụng một trong các phương thức FOR XML PATH được nêu chi tiết trong các câu trả lời khác.

Sử dụng COALESCE:

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

Chỉ cần một số lời giải thích (vì câu trả lời này dường như có được lượt xem tương đối thường xuyên):

  • Coalesce thực sự chỉ là một cheat hữu ích mà hoàn thành hai điều:

1) Không cần khởi tạo @Names với một giá trị chuỗi rỗng.

2) Không cần phải tách ra một dấu phân cách thêm ở cuối.

  • Giải pháp ở trên sẽ cho kết quả không chính xác nếu hàng có VÔ GIÁ TRỊ Giá trị tên (nếu có VÔ GIÁ TRỊ, các VÔ GIÁ TRỊ sẽ làm @Names  VÔ GIÁ TRỊ sau hàng đó và hàng tiếp theo sẽ bắt đầu lại như một chuỗi rỗng. Dễ dàng sửa với một trong hai giải pháp:
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

hoặc là:

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

Tùy thuộc vào hành vi bạn muốn (tùy chọn đầu tiên chỉ lọc VÔ GIÁ TRỊs ra, tùy chọn thứ hai giữ chúng trong danh sách với một thông điệp đánh dấu [thay thế 'N / A' với bất cứ điều gì là thích hợp cho bạn]).


895
2017-10-12 00:18



Để được rõ ràng, coalesce không có gì để làm với việc tạo danh sách, nó chỉ đảm bảo rằng các giá trị NULL không được bao gồm. - Graeme Perrow
@Graeme Perrow Nó không loại trừ các giá trị NULL (một WHERE là cần thiết cho điều đó - điều này sẽ mất kết quả nếu một trong các giá trị đầu vào là NULL) và được yêu cầu trong phương pháp này bởi vì: NULL + non-NULL -> NULL và non-NULL + NULL -> NULL; cũng @Name là NULL theo mặc định và, trên thực tế, thuộc tính đó được sử dụng như một sentinel ngầm ở đây để xác định xem ',' có nên được thêm vào hay không.
Xin lưu ý rằng phương pháp ghép nối này dựa trên SQL Server thực hiện truy vấn với một kế hoạch cụ thể. Tôi đã bị bắt gặp bằng cách sử dụng phương pháp này (với việc bổ sung một ORDER BY). Khi nó được giao dịch với một số lượng nhỏ các hàng nó làm việc tốt nhưng với nhiều dữ liệu SQL Server đã chọn một kế hoạch khác nhau mà kết quả trong việc chọn mục đầu tiên không có nối bất cứ điều gì. Xem bài viết này bởi Anith Sen. - fbarber
Phương thức này không thể được sử dụng như một truy vấn phụ trong một danh sách lựa chọn hoặc mệnh đề where, bởi vì nó sử dụng một biến tSQL. Trong những trường hợp như vậy, bạn có thể sử dụng các phương thức được cung cấp bởi @Ritesh - R. Schreurs
Đây không phải là phương pháp ghép nối đáng tin cậy. Nó không được hỗ trợ và không nên được sử dụng (theo Microsoft, ví dụ: support.microsoft.com/en-us/kb/287515, connect.microsoft.com/SQLServer/Feedback/Details/704389). Nó có thể thay đổi mà không cần cảnh báo. Sử dụng kỹ thuật XML PATH được thảo luận trong stackoverflow.com/questions/5031204/… Tôi đã viết thêm ở đây: marc.durdin.net/2015/07/… - Marc Durdin


Một phương pháp chưa được hiển thị qua XML  data() lệnh trong MS SQL Server là:

Giả sử bảng được gọi là NameList với một cột được gọi là FName,

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

trả về:

"Peter, Paul, Mary, "

Chỉ thêm dấu phẩy phải được xử lý.

Chỉnh sửa: Như được thông qua từ nhận xét của @ NReilingh, bạn có thể sử dụng phương pháp sau để xóa dấu phẩy sau. Giả sử cùng một tên bảng và cột:

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

297
2018-04-05 21:19



thánh s ** t thats tuyệt vời! Khi được thực thi, như trong ví dụ của bạn kết quả được định dạng là siêu liên kết, khi được nhấn (trong SSMS) sẽ mở một cửa sổ mới chứa dữ liệu, nhưng khi được sử dụng như một phần của truy vấn lớn hơn, nó chỉ xuất hiện dưới dạng một chuỗi. Nó là một chuỗi? hoặc là xml mà tôi cần phải xử lý khác nhau trong ứng dụng sẽ sử dụng dữ liệu này? - Ben
Cách tiếp cận này cũng có các ký tự thoát XML như <và>. Do đó, việc chọn '<b>' + FName + '</ b>' sẽ dẫn đến "& lt; b & gt; John & lt; / b & gt; & lt; b & gt; Paul ..." - Lukáš Lánský
Giải pháp gọn gàng. Tôi nhận thấy rằng ngay cả khi tôi không thêm + ', ' nó vẫn thêm một khoảng trống giữa mỗi phần tử ghép nối. - Baodad
@Baodad Điều đó dường như là một phần của thỏa thuận. Bạn có thể giải quyết sự cố bằng cách thay thế trên ký tự mã thông báo đã thêm. Ví dụ, điều này thực hiện một danh sách phân cách bằng dấu phẩy hoàn hảo cho bất kỳ độ dài nào: SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'') - NReilingh
Wow, thực sự trong thử nghiệm của tôi bằng cách sử dụng dữ liệu () và một thay thế là WAY hiệu quả hơn không. Siêu lạ. - NReilingh


Trong SQL Server 2005

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

Trong SQL Server 2016

bạn có thể dùng Cú pháp JSON

I E.

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

Và kết quả sẽ trở thành

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

Điều này sẽ làm việc ngay cả dữ liệu của bạn chứa các ký tự XML không hợp lệ

các '"},{"_":"' an toàn vì nếu dữ liệu của bạn chứa '"},{"_":"', nó sẽ được chuyển đến "},{\"_\":\"

Bạn có thể thay thế ', ' với bất kỳ dấu tách chuỗi nào


Và trong SQL Server 2017, Cơ sở dữ liệu SQL Azure

Bạn có thể sử dụng Hàm STRING_AGG


215
2018-03-14 05:00



Sử dụng tốt chức năng STUFF để nix hai ký tự dẫn đầu. - David
Tôi thích giải pháp này tốt nhất, bởi vì tôi có thể dễ dàng sử dụng nó trong một danh sách lựa chọn bằng cách gắn thêm 'as <label>'. Tôi không chắc chắn làm thế nào để làm điều này với các giải pháp của @ Ritesh. - R. Schreurs
Điều này tốt hơn câu trả lời được chấp nhận vì tùy chọn này cũng xử lý các ký tự reserverd un-escape XML chẳng hạn như <, >, &, v.v. FOR XML PATH('') sẽ tự động thoát. - BateTech
Đây là một phản ứng tuyệt vời vì nó giải quyết vấn đề và cung cấp những cách tốt nhất để làm việc trong các phiên bản khác nhau của SQL bây giờ tôi muốn tôi có thể sử dụng năm 2017 / Azure - Chris Ward


SQL Server 2017+ và SQL Azure: STRING_AGG

Bắt đầu với phiên bản tiếp theo của SQL Server, cuối cùng chúng ta có thể kết nối qua các hàng mà không cần phải sử dụng đến bất kỳ biến thể hoặc XML witchery nào.

STRING_AGG (Giao dịch-SQL)

Không có nhóm

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

Với nhóm:

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

Với phân nhóm và phân loại phụ

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

168
2017-10-12 00:10



Và, không giống như các giải pháp CLR, bạn có quyền kiểm soát việc phân loại. - canon
Điều này làm việc với SQL Azure. Câu trả lời chính xác! - user2721607
Điều này cũng làm việc cho tôi trong Azure SQL. Rực rỡ! - Kevin Stone


Trong MySQL có một hàm, GROUP_CONCAT (), cho phép bạn nối các giá trị từ nhiều hàng. Thí dụ:

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



Được sử dụng để yêu này, chưa thấy một sự thay thế cho chức năng này với bất kỳ Db nào khác! - Binoj Antony
Điều này hoàn toàn giải quyết được vấn đề của tôi. Tôi đã cố gắng để kéo tất cả các ngày thanh toán cho một khoản phí nhất định trên một tài khoản, điều này giải quyết nó hoàn hảo. Cảm ơn! - Maximus
hoạt động tốt. Nhưng khi tôi sử dụng SEPARATOR '", "' tôi sẽ bỏ lỡ một số ký tự ở cuối mục nhập cuối cùng. tại sao điều này có thể xảy ra? - gooleem
@ gooleem Tôi không rõ ràng về ý của bạn, nhưng chức năng này chỉ đặt dấu phân cách giữa các mục, chứ không phải sau. Nếu đó không phải là câu trả lời, tôi khuyên bạn nên đăng câu hỏi mới. - Darryl Hein
@ DarrylHein cho nhu cầu của tôi, tôi đã sử dụng dấu phân cách như trên. Nhưng điều này cắt giảm một số ký tự ở cuối đầu ra. Điều này rất lạ và có vẻ là một lỗi. Tôi không có một giải pháp, tôi chỉ cần giải quyết. - gooleem


Sử dụng COALESCE - - Tìm hiểu thêm từ đây

Ví dụ:

102

103

104

Sau đó viết mã dưới đây trong máy chủ 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

Đầu ra sẽ là:

102,103,104

52
2018-03-08 16:29



Đây thực sự là giải pháp IMO tốt nhất vì nó tránh được các vấn đề mã hóa mà FOR XML trình bày. Tôi đã sử dụng Declare @Numbers AS Nvarchar(MAX) và nó hoạt động tốt. Bạn có thể giải thích lý do tại sao bạn khuyên bạn không nên sử dụng nó? - EvilDr
Giải pháp này đã được đăng cách đây 8 năm! stackoverflow.com/a/194887/986862 - Andre Figueiredo
Tại sao truy vấn này trả về ??? ký hiệu thay vì chữ Kirin? Đây có phải là vấn đề đầu ra không? - Akmal Salikhov


Oracle 11g Release 2 hỗ trợ chức năng LISTAGG. Tài liệu đây.

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.

Cảnh báo

Hãy cẩn thận thực hiện chức năng này nếu có khả năng của chuỗi kết quả sẽ vượt quá 4000 ký tự. Nó sẽ ném một ngoại lệ. Nếu đó là trường hợp thì bạn cần phải xử lý các trường hợp ngoại lệ hoặc cuộn chức năng của riêng bạn mà ngăn chặn chuỗi tham gia từ hơn 4000 ký tự.


42
2017-08-09 21:20



Đối với các phiên bản cũ của Oracle, wm_concat là hoàn hảo. Việc sử dụng nó được giải thích trong món quà liên kết của Alex. Thnks Alex! - toscanelli
LISTAGG hoạt động hoàn hảo! Chỉ cần đọc tài liệu được liên kết tại đây. wm_concat bị xóa khỏi phiên bản 12c trở đi. - asgs


Postgres mảng là tuyệt vời. Thí dụ:

Tạo một số dữ liệu thử nghiệm:

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)

Tổng hợp chúng trong một mảng:

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

Chuyển đổi mảng thành chuỗi được phân cách bằng dấu phẩy:

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

LÀM XONG

Vì PostgreSQL 9.0 nó là dễ dàng hơn.


41
2017-07-06 12:46



Nếu bạn cần nhiều hơn một cột, ví dụ id nhân viên của họ trong ngoặc đơn sử dụng toán tử concat: select array_to_string(array_agg(name||'('||id||')' - Richard Fox
Không áp dụng cho sql-server, chỉ để mysql - GoldBishop


Trong SQL Server 2005 trở lên, hãy sử dụng truy vấn bên dưới để nối các hàng.

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



Tôi tin rằng điều này không thành công khi các giá trị chứa các ký hiệu XML như < hoặc là &. - Sam