Aggregate

STRING_AGG

BigQuery

Menggabungkan nilai string dari beberapa baris menjadi satu string dengan delimiter tertentu. Sangat berguna untuk membuat daftar atau comma-separated values di BigQuery.

Tipe hasil: STRINGDiperbarui: 7 Jan 2026

Syntax

SQL

STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key])

Parameter

expressionSTRINGwajib

Kolom atau ekspresi string yang akan digabungkan

delimiterSTRINGopsional

Pemisah antar nilai (default: koma ',')

Default: ','

DISTINCTmodifieropsional

Hanya gabungkan nilai unik

ORDER BYclauseopsional

Mengurutkan nilai sebelum digabungkan

Contoh Penggunaan

Menggabungkan Nama Produk

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;

Membuat daftar produk yang dibeli per customer.

Hasil

customer_id	purchased_products
C001	Laptop, Mouse, Keyboard
C002	Monitor, USB Hub
C003	Headphone, Webcam, Microphone

STRING_AGG dengan 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;

Menggabungkan nama karyawan diurutkan berdasarkan tanggal masuk.

Hasil

department	employees_by_seniority
Engineering	Alice; Bob; Charlie; Diana
Marketing	Eve; Frank; Grace

STRING_AGG dengan 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;

Menampilkan kategori unik yang terjual per hari.

Hasil

order_date	categories_sold
2024-06-25	Books, Electronics, Fashion
2024-06-24	Electronics, Food, Home
2024-06-23	Books, Fashion, Sports

Membuat 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;

Membuat format JSON array dari tags.

Hasil

user_id	tags_json
U001	["python", "sql", "data"]
U002	["javascript", "react"]

Fungsi Terkait

ARRAY_AGGCONCAT

Kembali ke BigQuery Functions

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;

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;

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;

1	SELECT
2	user_id,
3	'[' \|\| STRING_AGG('"' \|\| tag \|\| '"', ', ') \|\| ']' as tags_json
4	FROM `project.dataset.user_tags`
5	GROUP BY user_id;