SQLiteのGROUP_CONCAT関数で複数列を結合する方法：注文履歴をカンマ区切りで表示

2024-04-15

SQLiteにおける GROUP_CONCAT 関数で複数列を結合する方法

基本的な構文

SELECT
  group_column,
  GROUP_CONCAT(separator column1, column2, ...)
FROM table_name
GROUP BY group_column;

例：顧客ごとの注文履歴

顧客テーブル customers と注文テーブル orders を用いて、顧客ごとの注文履歴を結合してみましょう。

SELECT
  customers.name AS 顧客名,
  GROUP_CONCAT(orders.product, ', ') AS 注文履歴
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

このクエリは、以下の結果を返します。

顧客名	注文履歴
山田太郎	Tシャツ, ズボン, 靴
佐藤花子	バッグ, 財布

複数の区切り文字

区切り文字を複数指定することで、より柔軟な結合が可能です。例えば、カンマと改行を組み合わせることで、各注文履歴を改行で区切ることができます。

SELECT
  customers.name AS 顧客名,
  GROUP_CONCAT(orders.product, ', \n') AS 注文履歴
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

列の選択と並べ替え

GROUP_CONCAT 関数内では、結合する列を選択し、並び替えることもできます。

SELECT
  customers.name AS 顧客名,
  GROUP_CONCAT(orders.product ORDER BY orders.price DESC) AS 注文履歴_価格順
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

このクエリは、顧客ごとの注文履歴を価格の高い順に並べて返します。

DISTINCT キーワードを併用することで、重複する値を除外することができます。

SELECT
  customers.name AS 顧客名,
  GROUP_CONCAT(DISTINCT orders.category) AS 購入カテゴリ
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

このクエリは、顧客が購入したカテゴリを重複なく表示します。

補足

GROUP_CONCAT 関数は、結合する列のデータ型が一致している必要があります。
長い文字列を結合する場合は、MAXLENGTH オプションで文字列の長さを制限することができます。

SQLiteにおける GROUP_CONCAT 関数を使用したサンプルコード

-- テーブル定義
CREATE TABLE customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  product TEXT NOT NULL,
  price REAL NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- サンプルデータ挿入
INSERT INTO customers (name) VALUES
  ('山田太郎'),
  ('佐藤花子');

INSERT INTO orders (customer_id, product, price) VALUES
  (1, 'Tシャツ', 1000),
  (1, 'ズボン', 2000),
  (1, '靴', 3000),
  (2, 'バッグ', 5000),
  (2, '財布', 2000);

-- 顧客ごとの注文履歴を表示
SELECT
  customers.name AS 顧客名,
  GROUP_CONCAT(orders.product, ', ') AS 注文履歴
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

出力結果

顧客名 | 注文履歴
-------|-----------
山田太郎 | Tシャツ, ズボン, 靴
佐藤花子 | バッグ, 財布

説明

このサンプルコードでは、以下の操作を行います。

customers と orders という2つのテーブルを作成します。
サンプルデータをそれぞれのテーブルに挿入します。
GROUP_CONCAT 関数を使用して、顧客ごとの注文履歴を結合して表示します。

ポイント

GROUP_CONCAT 関数は、SELECT クエリ内でグループ化されたデータに対して使用することができます。
区切り文字を複数指定することで、より柔軟な結合が可能です。
列の選択と並べ替えも可能です。

商品ごとのカテゴリ情報
著者ごとの書籍情報
部署ごとの従業員情報

これらのサンプルコードを参考に、GROUP_CONCAT 関数を活用して、必要な情報を効率的に取得してください。

SQLiteにおける GROUP_CONCAT 関数以外の複数列結合方法

サブクエリを使用して、結合する列を1つの列にまとめることができます。

SELECT
  group_column,
  (
    SELECT GROUP_CONCAT(column1, ', ')
    FROM table_name AS sub
    WHERE sub.group_column = table_name.group_column
  ) AS combined_column
FROM table_name
GROUP BY group_column;

例

顧客ごとの注文履歴をサブクエリで結合してみましょう。

SELECT
  customers.name AS 顧客名,
  (
    SELECT GROUP_CONCAT(orders.product, ', ')
    FROM orders AS sub
    WHERE sub.customer_id = customers.id
  ) AS 注文履歴
FROM customers;

STRING_AGG 関数を使用する (SQLite 3.31.0 以降)

SQLite 3.31.0 以降では、STRING_AGG 関数を使用して複数列を結合することができます。この関数は、GROUP_CONCAT 関数よりも柔軟な機能を提供します。

SELECT
  group_column,
  STRING_AGG(column1, ', ') WITHIN GROUP (ORDER BY column1) AS combined_column
FROM table_name
GROUP BY group_column;

顧客ごとの注文履歴を STRING_AGG 関数で結合し、注文履歴を価格の高い順に並べてみましょう。

SELECT
  customers.name AS 顧客名,
  STRING_AGG(orders.product, ', ') WITHIN GROUP (ORDER BY orders.price DESC) AS 注文履歴_価格順
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

列を結合する新しいテーブルを作成する

一時的なテーブルを作成して、列を結合する方法もあります。この方法は、複雑な結合が必要な場合に役立ちます。

-- 一時テーブルの作成
CREATE TEMPORARY TABLE order_history (
  customer_id INTEGER,
  order_history TEXT
);

-- 注文履歴を一時テーブルに挿入
INSERT INTO order_history (customer_id, order_history)
SELECT
  orders.customer_id,
  GROUP_CONCAT(orders.product, ', ')
FROM orders
GROUP BY orders.customer_id;

-- 顧客情報と注文履歴を結合
SELECT
  customers.name AS 顧客名,
  order_history.order_history AS 注文履歴
FROM customers
JOIN order_history ON customers.id = order_history.customer_id;

-- 一時テーブルを削除
DROP TABLE order_history;

各方法の比較

方法	利点	欠点	備考
`GROUP_CONCAT` 関数	シンプルでわかりやすい	区切り文字が必須
サブクエリ	柔軟性が高い	構文が複雑になる
`STRING_AGG` 関数 (SQLite 3.31.0 以降)	`GROUP_CONCAT` 関数よりも柔軟	SQLite 3.31.0 以降でのみ利用可能
結合テーブル	複雑な結合が可能	処理が重くなる可能性がある	一時テーブルを使用する場合は、後で削除する必要