Aggregate
BigQuery
STRING_AGG
BigQueryConcatenates string values from multiple rows into a single string with a specified delimiter. Very useful for creating lists or comma-separated values in BigQuery.
Tipe hasil:
STRINGDiperbarui: 13 Jun 2026Syntax
SQL
STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key])Parameter
expressionSTRINGwajib
A string column or expression to concatenate
delimiterSTRINGopsional
Separator between values (default: comma ',')
Default: ','
DISTINCTmodifieropsional
Concatenate only unique values
ORDER BYclauseopsional
Sort values before concatenating
Contoh Penggunaan
Concatenate Product Names
SQL
1 SELECT 2 customer_id, 3 STRING_AGG(product_name, ', ') as purchased_products 4 FROM `project.dataset.orders` 5 GROUP BY customer_id 6 LIMIT 3;
Creates a list of products purchased per customer.
Hasil
| customer_id | purchased_products |
|---|---|
| C001 | Laptop, Mouse, Keyboard |
| C002 | Monitor, USB Hub |
| C003 | Headphone, Webcam, Microphone |
STRING_AGG with ORDER BY
SQL
1 SELECT 2 department, 3 STRING_AGG(employee_name, '; ' ORDER BY hire_date) as employees_by_seniority 4 FROM `project.dataset.employees` 5 GROUP BY department;
Concatenates employee names sorted by their hire date.
Hasil
| department | employees_by_seniority |
|---|---|
| Engineering | Alice; Bob; Charlie; Diana |
| Marketing | Eve; Frank; Grace |
STRING_AGG with DISTINCT
SQL
1 SELECT 2 order_date, 3 STRING_AGG(DISTINCT category, ', ' ORDER BY category) as categories_sold 4 FROM `project.dataset.sales` 5 GROUP BY order_date 6 ORDER BY order_date DESC 7 LIMIT 3;
Displays unique categories sold per day.
Hasil
| order_date | categories_sold |
|---|---|
| 2024-06-25 | Books, Electronics, Fashion |
| 2024-06-24 | Electronics, Food, Home |
| 2024-06-23 | Books, Fashion, Sports |
Build JSON-like String
SQL
1 SELECT 2 user_id, 3 '[' || STRING_AGG('"' || tag || '"', ', ') || ']' as tags_json 4 FROM `project.dataset.user_tags` 5 GROUP BY user_id;
Builds a JSON array format from tags.
Hasil
| user_id | tags_json |
|---|---|
| U001 | ["python", "sql", "data"] |
| U002 | ["javascript", "react"] |
Pertanyaan Umum tentang STRING_AGG
Apa itu fungsi STRING_AGG di BigQuery?
Concatenates string values from multiple rows into a single string with a specified delimiter. Very useful for creating lists or comma-separated values in BigQuery. Di BigQuery, fungsi STRING_AGG termasuk dalam kelompok fungsi yang sering digunakan untuk mengolah dan menganalisis data secara efisien.
Bagaimana cara menggunakan STRING_AGG di BigQuery?
Gunakan sintaks berikut: STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key]). Pastikan argumen yang dimasukkan sudah sesuai dengan tipe data yang diharapkan.
Apa nilai yang dikembalikan oleh fungsi STRING_AGG?
Fungsi STRING_AGG mengembalikan nilai bertipe STRING. Pastikan tipe data hasil sudah sesuai dengan kebutuhan query atau formula kamu. Jika input mengandung nilai NULL, perilaku fungsi dapat berbeda — selalu periksa dokumentasi untuk memastikan hasilnya sesuai ekspektasi.
Apa saja parameter fungsi STRING_AGG?
Fungsi ini memiliki 1 parameter wajib dan 3 parameter opsional. Parameter yang digunakan: expression (STRING, wajib): A string column or expression to concatenate; delimiter (STRING, opsional): Separator between values (default: comma ','); DISTINCT (modifier, opsional): Concatenate only unique values; ORDER BY (clause, opsional): Sort values before concatenating.
Ngulik Langsung
Latihan Langsung →Sudah paham STRING_AGG? Latih langsung di browser
Latihan interaktif, langsung di browser.