顧客と注文のリストをGROUP_CONCATで簡単に作成！SQL Serverでできるテクニック

SQL Server で GROUP_CONCAT を使ってクエリを作成する方法

SQL Server には、複数の値を 1 つの列に結合するための組み込み関数 GROUP_CONCAT が用意されています。これは、関連するレコードのリストをカンマ区切りなどで連結する際に役立ちます。

構文

GROUP_CONCAT(DISTINCT [column_name]
              [ORDER BY column_name [ASC | DESC]])

引数

• column_name: 結合する列の名前
• DISTINCT: 同じ値を重複せずに表示します (省略可)
• ORDER BY: 結合する順序を指定します (省略可)

例

顧客テーブル を例として考えてみましょう。このテーブルには、顧客 ID、名前、住所、および注文 ID の列が含まれています。各顧客は複数の注文を持つことができます。

顧客と注文のリスト を作成するには、次のクエリを使用します。

SELECT c.customer_id, c.name, c.address,
       GROUP_CONCAT(DISTINCT o.order_id) AS order_ids
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.address;

このクエリは、各顧客の顧客 ID、名前、住所、および注文 ID のリストを返します。注文 ID はカンマ区切りで連結されます。

GROUP_CONCAT 関数は、さまざまな状況で使用できます。以下に、いくつかの例を示します。

• 特定の顧客が購入したすべての商品のリストを取得する
• 特定のカテゴリに属するすべての商品のリストを取得する
• 特定のステータスを持つすべての注文のリストを取得する

• GROUP_CONCAT 関数は、長い文字列を返す可能性があります。結果セットのサイズが大きくなる可能性がある場合は、注意が必要です。
• GROUP_CONCAT 関数は、パフォーマンスに影響を与える可能性があります。大量のデータに対して使用する場合は、注意が必要です。
• 代わりに STRING_AGG 関数を使用することもできます。STRING_AGG 関数は、GROUP_CONCAT 関数よりも新しいもので、より多くの機能を提供します。

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  address VARCHAR(255) NOT NULL
);

注文テーブル

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

データ挿入

INSERT INTO customers (customer_id, name, address)
VALUES
  (1, 'John Doe', '123 Main Street'),
  (2, 'Jane Doe', '456 Elm Street'),
  (3, 'Peter Jones', '789 Oak Street');

INSERT INTO orders (order_id, customer_id)
VALUES
  (1, 1),
  (2, 1),
  (3, 2),
  (4, 3),
  (5, 3);

クエリ

SELECT c.customer_id, c.name, c.address,
       GROUP_CONCAT(DISTINCT o.order_id) AS order_ids
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.address;

結果

customer_id | name          | address        | order_ids
----------- | ------------- | -------------- | --------
1           | John Doe      | 123 Main Street | 1, 2
2           | Jane Doe      | 456 Elm Street  | 3
3           | Peter Jones   | 789 Oak Street | 4, 5

説明

このクエリは、customers テーブルと orders テーブルを結合し、各顧客の顧客 ID、名前、住所、および注文 ID のリストを返します。注文 ID はカンマ区切りで連結されます。

GROUP_CONCAT 関数は、customers テーブルの各行に対して、orders テーブルから関連するすべての order_id をグループ化し、カンマ区切りで連結します。DISTINCT キーワードは、重複する値を削除します。

方法

SELECT c.customer_id, c.name, c.address,
       (SELECT STUFF((SELECT ',' + o.order_id
                      FROM orders o
                      WHERE o.customer_id = c.customer_id
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
        ) AS order_ids
FROM customers c;

このクエリは、FOR XML PATH 句を使用して、orders テーブルから関連するすべての order_id をカンマ区切りで連結した文字列を作成します。STUFF 関数は、この文字列の先頭にカンマを追加し、余分なカンマを削除します。

結果 表は、GROUP_CONCAT を使用したクエリと同じ結果を返します。

• FOR XML PATH 句は、複雑なクエリになる可能性があります。
• STUFF 関数は、特定の位置に文字列を挿入する必要がある場合に役立ちます。
• REPLACE 関数は、文字列の一部を別の文字列に置き換える必要がある場合に役立ちます。
• CONVERT 関数は、データ型を別のデータ型に変換する必要がある場合に役立ちます。