STRING_AGG (T-SQL)
Berlaku untuk:
SQL Server 2017 (14.x) dan yang lebih baru Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics
Menggabungkan nilai ekspresi string dan menempatkan nilai pemisah di antaranya. Pemisah tidak ditambahkan di akhir string.
Sintaks
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Catatan
Untuk melihat sintaks transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
ekspresi
Adalah ekspresi dari jenis apa pun. Ekspresi dikonversi ke NVARCHAR atau VARCHAR jenis selama perangkaian. Jenis non-string dikonversi menjadi NVARCHAR jenis.
pemisah
Adalah ekspresiNVARCHAR atau VARCHAR jenis yang digunakan sebagai pemisah untuk string yang digabungkan. Ini bisa harfiah atau variabel.
<order_clause>
Secara opsional tentukan urutan hasil yang digabungkan menggunakan WITHIN GROUP klausa:
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
<order_by_expression_list>
Daftar ekspresi non-konstan yang dapat digunakan untuk mengurutkan hasil. Hanya satu order_by_expression yang diizinkan per kueri. Urutan sortir default adalah menaik.
Jenis Pengembalian
Jenis pengembalian tergantung pada argumen pertama (ekspresi). Jika argumen input adalah jenis string (NVARCHAR, VARCHAR), jenis hasil akan sama dengan jenis input. Tabel berikut ini mencantumkan konversi otomatis:
| Jenis ekspresi input | Hasil |
|---|---|
| NVARCHAR(MAX) | NVARCHAR(MAX) |
| VARCHAR(MAX) | VARCHAR(MAX) |
| NVARCHAR(1...4000) | NVARCHAR(4000) |
| VARCHAR(1...8000) | VARCHAR(8000) |
| int, bigint, smallint, tinyint, numerik, float, nyata, bit, desimal, smallmoney, uang, datetime, datetime2, | NVARCHAR(4000) |
Keterangan
STRING_AGG adalah fungsi agregat yang mengambil semua ekspresi dari baris dan menggabungkannya menjadi satu string. Nilai ekspresi secara implisit dikonversi ke jenis string lalu digabungkan. Konversi implisit ke string mengikuti aturan yang ada untuk konversi jenis data. Untuk informasi selengkapnya tentang konversi jenis data, lihat CAST dan CONVERT (Transact-SQL).
Jika ekspresi input adalah jenis VARCHAR, pemisah tidak dapat berupa jenis NVARCHAR.
Nilai null diabaikan dan pemisah terkait tidak ditambahkan. Untuk mengembalikan place holder untuk nilai null, gunakan fungsi seperti yang ISNULL ditunjukkan dalam contoh B.
STRING_AGG tersedia dalam tingkat kompatibilitas apa pun.
Catatan
<order_clause> tersedia dengan tingkat kompatibilitas database 110 ke atas.
Contoh
Sebagian besar contoh dalam artikel ini mereferensikan database sampel AdventureWorks.
J. Hasilkan daftar nama yang dipisahkan dalam baris baru
Contoh berikut menghasilkan daftar nama dalam sel hasil tunggal, dipisahkan dengan pengembalian pengangkutan.
USE AdventureWorks2019
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), CHAR(13)) AS csv
FROM Person.Person;
GO
Berikut adalah hasil yang ditetapkan.
| CSV |
|---|
| Syed Catherine Kim Kim Kim Kabut asap ... |
NULL nilai yang ditemukan dalam name sel tidak dikembalikan dalam hasil.
Catatan
Jika menggunakan SQL Server Management Studio Editor Kueri, opsi Hasil ke Kisi tidak dapat menerapkan pengembalian pengangkutan. Beralih ke Hasil ke Teks untuk melihat tataan hasil dengan benar.
Hasil ke Teks dipotok menjadi 256 karakter secara default. Untuk menambah batas ini, ubah opsi Jumlah maksimum karakter yang ditampilkan di setiap kolom .
B. Hasilkan daftar nama yang dipisahkan dengan koma tanpa nilai NULL
Contoh berikut mengganti nilai null dengan 'N/A' dan mengembalikan nama yang dipisahkan dengan koma dalam sel hasil tunggal.
USE AdventureWorks2019
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(FirstName,'N/A')), ',') AS csv
FROM Person.Person;
GO
Berikut adalah hasil yang ditetapkan.
Catatan
Hasil ditampilkan dipangkas.
| CSV |
|---|
| Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar, ... |
C. Hasilkan nilai yang dipisahkan koma
USE AdventureWorks2019
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names
FROM Person.Person;
GO
Berikut adalah hasil yang ditetapkan.
Catatan
Hasil ditampilkan dipangkas.
| names |
|---|
| Ken Sánchez (8 Feb 2003 12:00) Terri Duffy (24 Feb 2002 12:00AM) Roberto Tamburello (5 Des 2001 12:00) Rob Walters (29 Des 2001 12:00) ... |
Catatan
Jika menggunakan Management Studio Editor Kueri, opsi Hasil ke Kisi tidak dapat menerapkan pengembalian pengangkutan. Beralih ke Hasil ke Teks untuk melihat tataan hasil dengan benar.
D. Mengembalikan artikel berita dengan tag terkait
Bayangkan database tempat artikel dan tagnya dipisahkan menjadi tabel yang berbeda. Pengembang ingin mengembalikan satu baris per setiap artikel dengan semua tag terkait. Kueri berikut mencapai hasil ini:
SELECT a.articleId, title, STRING_AGG (tag, ',') as tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;
GO
Berikut adalah hasil yang ditetapkan.
| articleId | judul | tag |
|---|---|---|
| 172 | Jajak pendapat menunjukkan hasil pemilu yang tertutup | politik, jajak pendapat, dewan kota |
| 176 | Jalan raya baru diperkirakan akan mengurangi kemacetan | NULL |
| 177 | Anjing terus lebih populer daripada kucing | polling, hewan |
Catatan
Klausa GROUP BY diperlukan jika fungsi bukan satu-satunya STRING_AGG item dalam SELECT daftar.
E. Membuat daftar email per kota
Kueri berikut menemukan alamat email karyawan dan mengelompokkannya menurut kota:
USE AdventureWorks2019
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') AS emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
Berikut adalah hasil yang ditetapkan.
Catatan
Hasil ditampilkan dipangkas.
| Kota | |
|---|---|
| Ballard | paige28@adventure-works.com;joshua24@adventure-works.com;javier12@adventure-works.com;... |
| Baltimore | gilbert9@adventure-works.com |
| Barstow | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Baytown | kelvin15@adventure-works.com |
| Beaverton | billy6@adventure-works.com;dalton35@adventure-works.com;lawrence1@adventure-works.com;... |
| Bell Gardens | christy8@adventure-works.com |
| Bellevue | min0@adventure-works.com;gigi0@adventure-works.com;terry18@adventure-works.com;... |
| Bellflower | philip0@adventure-works.com;emma34@adventure-works.com;jorge8@adventure-works.com;... |
| Bellingham | christopher23@adventure-works.com;frederick7@adventure-works.com;omar0@adventure-works.com;... |
Email yang dikembalikan di kolom email dapat langsung digunakan untuk mengirim email ke grup orang yang bekerja di beberapa kota tertentu.
F. Membuat daftar email yang diurutkan per kota
Mirip dengan contoh sebelumnya, kueri berikut menemukan alamat email karyawan, mengelompokkannya menurut kota, dan mengurutkan email menurut abjad:
USE AdventureWorks2019
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS Emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
Berikut adalah hasil yang ditetapkan.
Catatan
Hasil ditampilkan dipangkas.
| Kota | |
|---|---|
| Barstow | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Braintree | mindy20@adventure-works.com |
| Bell Gardens | christy8@adventure-works.com |
| Byron | louis37@adventure-works.com |
| Bordeaux | ranjit0@adventure-works.com |
| Carnation | don0@adventure-works.com;douglas0@adventure-works.com;george0@adventure-works.com;... |
| Boulogne-Billancourt | allen12@adventure-works.com;bethany15@adventure-works.com;carl5@adventure-works.com;... |
| Berkshire | barbara41@adventure-works.com;brenda4@adventure-works.com;carrie14@adventure-works.com;... |
| Berks | adriana6@adventure-works.com;alisha13@adventure-works.com;arthur19@adventure-works.com;... |
Langkah berikutnya
Pelajari selengkapnya tentang fungsi transact-SQL di artikel berikut: