Pertanyaan Bagaimana menggabungkan teks dari beberapa baris menjadi satu string teks di SQL server?


Pertimbangkan tabel database memegang nama, dengan tiga baris:

Peter
Paul
Mary

Apakah ada cara mudah untuk mengubah ini menjadi string tunggal Peter, Paul, Mary?


1483
2017-10-11 23:49


asal


Jawaban:


Jika Anda berada di SQL Server 2017 atau Azure, lihat Jawaban Mathieu Renda.

Saya memiliki masalah yang sama ketika saya mencoba menggabungkan dua tabel dengan hubungan satu ke banyak. Di SQL 2005 saya menemukan itu XML PATH metode dapat menangani rentetan baris dengan sangat mudah.

Jika ada meja yang disebut STUDENTS

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

Hasil yang saya harapkan adalah:

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

Saya menggunakan yang berikut ini 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]

Anda dapat melakukan hal yang sama dengan cara yang lebih ringkas jika Anda dapat menggabungkan koma di awal dan gunakan substring untuk melewati yang pertama sehingga Anda tidak perlu melakukan sub-query:

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

1105
2018-02-13 11:53



Jawaban ini dapat mengembalikan hasil yang tidak diharapkan ketika ada klausa ORDER BY. Untuk hasil yang konsisten, gunakan salah satu metode XML PATH yang dirinci dalam jawaban lainnya.

Menggunakan COALESCE:

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

Hanya beberapa penjelasan (karena jawaban ini tampaknya mendapatkan pandangan yang relatif teratur):

  • Coalesce benar-benar hanya penipu yang bermanfaat yang menyelesaikan dua hal:

1) Tidak perlu menginisialisasi @Names dengan nilai string kosong.

2) Tidak perlu melepas pemisah ekstra di bagian akhir.

  • Solusi di atas akan memberikan hasil yang salah jika sebuah baris memiliki BATAL Nilai nama (jika ada BATAL, yang BATAL akan membuat @Names  BATAL setelah baris itu, dan baris berikutnya akan mulai lagi sebagai string kosong lagi. Mudah diperbaiki dengan salah satu dari dua solusi:
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

atau:

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

Tergantung pada perilaku apa yang Anda inginkan (opsi pertama hanya menyaring BATALdi luar, opsi kedua membuat mereka dalam daftar dengan pesan penanda [ganti 'N / A' dengan apa pun yang sesuai untuk Anda]).


893
2017-10-12 00:18



Satu metode belum ditampilkan melalui XML  data() perintah di MS SQL Server adalah:

Asumsikan tabel yang disebut NameList dengan satu kolom bernama FName,

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

kembali:

"Peter, Paul, Mary, "

Hanya ekstra koma yang harus ditangani.

Edit: Seperti yang diadopsi dari komentar @ NReilingh, Anda dapat menggunakan metode berikut untuk menghapus koma trailing. Dengan asumsi nama tabel dan kolom yang sama:

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

296
2018-04-05 21:19



Di SQL Server 2005

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

Di SQL Server 2016

Anda bisa menggunakan UNTUK sintaks JSON

yaitu

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

Dan hasilnya akan menjadi

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

Ini akan berfungsi bahkan data Anda mengandung karakter XML yang tidak valid

itu '"},{"_":"' aman karena jika data Anda mengandung '"},{"_":"', itu akan melarikan diri "},{\"_\":\"

Anda dapat mengganti ', ' dengan pemisah string apa pun


Dan dalam SQL Server 2017, Azure SQL Database

Anda dapat menggunakan yang baru STRING_AGG berfungsi


214
2018-03-14 05:00



SQL Server 2017+ dan SQL Azure: STRING_AGG

Dimulai dengan versi SQL Server berikutnya, kita akhirnya bisa menggabungkan seluruh baris tanpa harus menggunakan sembarang variabel atau XML witchery.

STRING_AGG (Transact-SQL)

Tanpa pengelompokan

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

Dengan pengelompokan:

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

Dengan pengelompokan dan sub-penyortiran

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

167
2017-10-12 00:10



Di MySQL ada fungsi, GROUP_CONCAT (), yang memungkinkan Anda menggabungkan nilai dari beberapa baris. Contoh:

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



Menggunakan BERSATU - Pelajari lebih lanjut dari sini

Sebagai contoh:

102

103

104

Kemudian tulis kode di bawah ini di server 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

Outputnya adalah:

102,103,104

52
2018-03-08 16:29



Oracle 11g Release 2 mendukung fungsi LISTAGG. Dokumentasi sini.

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.

PERINGATAN

Hati-hati menerapkan fungsi ini jika ada kemungkinan string yang dihasilkan melebihi 4000 karakter. Itu akan melempar pengecualian. Jika itu yang terjadi maka Anda perlu baik menangani pengecualian atau menggulirkan fungsi Anda sendiri yang mencegah string yang tergabung dari lebih dari 4000 karakter.


42
2017-08-09 21:20