例えば left join などで集約したデータを作成していたらこのIDでひとつにまとめたい、でも item カラムのどちらの行の情報も残したいなという時があると思います。そんな時にカラムを1つの行にまとめて表示できないかなと思い調べてみました。
やりたいこと
いかのような項目があったとします。
| 2 | アイマスク |
| 2 | ティッシュ |
それを以下のようにまとめたい。
| 2 | アイマスク, ティッシュ |
動作環境
SQLiteonlineとSQL FIDDLEでPostgreSQL9.6、MS SQL server2017 (日本語は文字化け) で動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。
複数行のカラムをstring_agg()で１つの文字列にまとめる方法
サンプルコードとともにどのように複数行を１つにまとめるか詳しく書いていきます。
string_agg()
複数行を一つにまとめるために string_agg() を利用します。
string_agg() は入力された値を指定したデリミタで区切られた一つの文字列に連結してくれます。
構文
string_agg(expression, delimiter)
引数は(text, text)または(bytea, bytea)に対応しています。数値は無理なようです。
今回は (text, text)で expression にカラムを入れます。delimiter （デリミタ）には区切り文字が入ります。
実際に複数行のカラムを１つにまとめてみる！
実際に複数行のカラムを１つにまとめるサンプルコードとともに動きを確認してみましょう。
準備
使用するテーブルは以下になります。
| parcei_cd | item |
|---|---|
| 1 | 参考書 |
| 2 | アイマスク |
| 2 | ティッシュ |
| 2 | 目薬 |
| 3 | キーボード |
| 3 | マウス |
上記テーブルを作成する文です。
— CREATE文
CREATE TABLE delivery (
parceI_cd numeric(8, 0) not null,
item character varying (20) not null
);
— INSERT文
insert into delivery
values(0001, ‘参考書’),
(0002, ‘アイマスク’),
(0002, ‘ティッシュ’),
(0002, ‘目薬’),
(0003, ‘キーボード’),
(0003, ‘マウス’);
サンプルコード
上記のテーブルを使って出荷する品物(item)を１つの箱(parcei_cd)に入れるもので１行にまとめるイメージでSELECT文を作成したいと思います。
作成した SELECT 文
SELECT
parceI_cd,
string_agg( item, ‘, ‘) as item_list
FROM
delivery
GROUP BY
parcei_cd
結果
| parcei_cd | item_list |
|---|---|
| 1 | 参考書 |
| 2 | アイマスク, ティッシュ, 目薬 |
| 3 | キーボード, マウス |
カンマ区切りで itemカラムの項目がまとめられました！GRUP BYでまとめられた項目を羅列してくれます。
おわり
実際に仕事でこのケースに当たった時はこのまとめる前のテーブルを作成するので複数テーブルから情報を集めてきた後だったので、その後にstring_agg()で文字列をまとめることはさほど難しく感じませんでした。
