字串聚合函數 (SQL String Aggregation)

字串聚合函數用來將多筆資料的字串值合併成一個字串，常搭配 GROUP BY 使用。不同資料庫有不同的函數名稱。

各資料庫語法

資料庫函數
MySQLGROUP_CONCAT()
SQL Server 2017+STRING_AGG()
PostgreSQLSTRING_AGG()
OracleLISTAGG()

範例資料

假設有一個訂單資料表 orders：

customer_idproduct
1蘋果
1香蕉
1橘子
2葡萄
2西瓜

MySQL - GROUP_CONCAT()

SELECT customer_id, GROUP_CONCAT(product) AS products
FROM orders
GROUP BY customer_id;

結果：

customer_idproducts
1蘋果,香蕉,橘子
2葡萄,西瓜

自訂分隔符號

SELECT customer_id, GROUP_CONCAT(product SEPARATOR ' / ') AS products
FROM orders
GROUP BY customer_id;

結果：蘋果 / 香蕉 / 橘子

排序後合併

SELECT customer_id, GROUP_CONCAT(product ORDER BY product ASC) AS products
FROM orders
GROUP BY customer_id;

去除重複值

SELECT customer_id, GROUP_CONCAT(DISTINCT product) AS products
FROM orders
GROUP BY customer_id;

SQL Server - STRING_AGG()

SQL Server 2017 以後版本支援：

SELECT customer_id, STRING_AGG(product, ',') AS products
FROM orders
GROUP BY customer_id;

排序後合併

SELECT customer_id, 
       STRING_AGG(product, ',') WITHIN GROUP (ORDER BY product) AS products
FROM orders
GROUP BY customer_id;

PostgreSQL - STRING_AGG()

SELECT customer_id, STRING_AGG(product, ',') AS products
FROM orders
GROUP BY customer_id;

排序後合併

SELECT customer_id, STRING_AGG(product, ',' ORDER BY product) AS products
FROM orders
GROUP BY customer_id;

Oracle - LISTAGG()

SELECT customer_id, LISTAGG(product, ',') WITHIN GROUP (ORDER BY product) AS products
FROM orders
GROUP BY customer_id;

常見用途

  1. 報表呈現：將多筆關聯資料顯示在同一行
  2. 標籤列表：將商品的多個標籤合併顯示
  3. 收件者清單：將多個 email 合併成一個字串