やりたいこと
以下のようなテーブルがある場合、
peopleテーブル telテーブル
| id | name | | id | people_id | tel |
|----|------| |----|-----------|---------------|
| 1 | hoge | | 1 | 1 | 090-0000-1111 |
| 2 | fuga | | 2 | 1 | 090-0000-2222 |
| 3 | foo | | 3 | 2 | 090-0000-3333 |
| 4 | bar | | 4 | 3 | 090-0000-4444 |
以下の結果を得たい
| id | tel1 | tel2 |
|----|---------------|---------------|
| 1 | 090-0000-1111 | 090-0000-2222 |
| 2 | 090-0000-3333 | null |
| 3 | 090-0000-4444 | null |
| 4 | null | null |
やり方
1. 複数レコードを1レコード・1カラムにする
GROUP BYでpeople.id単位でまとめ、同じpeople.idでtelが複数ある場合は配列として1つのカラムに格納する
SELECT
p.id,
ARRAY_AGG(t.tel) AS concat_tel
FROM people p
LEFT JOIN tel t
on p.id = t.people_id
GROUP BY p.id
結果↓
| id | concat_tel |
|----|-------------------------------|
| 1 | {090-0000-1111,090-0000-2222} |
| 2 | {090-0000-3333} |
| 3 | {090-0000-4444} |
| 4 | {null} |
2. 配列を複数カラムに分割する
WITH concat_tel_table AS ( -- 1で作成したSQL
SELECT
p.id,
ARRAY_AGG(t.tel) AS concat_tel
FROM people p
LEFT JOIN tel t
on p.id = t.people_id
GROUP BY p.id
)
SELECT
ctt.id,
ctt.concat_tel[1] AS tel1,
ctt.concat_tel[2] AS tel2
FROM concat_tel_table ctt
↓結果
| id | tel1 | tel2 |
|----|---------------|---------------|
| 1 | 090-0000-1111 | 090-0000-2222 |
| 2 | 090-0000-3333 | null |
| 3 | 090-0000-4444 | null |
| 4 | null | null |
あとがき
より良いやり方や、既に作られている車輪があれば教えて頂けるとありがたいです。
(追記)
修正前はARRAY_AGG
で配列にしたものをわざわざARRAY_TO_STRING
で,
区切りの文字列にし、
それをREGEXP_SPLIT_TO_ARRAY
で再度配列に戻すという無駄なことをしていたので修正しました。
playground
https://www.db-fiddle.com/f/ub6Hhyk81Ud6qaXdRMK2j2/0
schema
CREATE TABLE people(
id SERIAL,
name TEXT
);
INSERT INTO people VALUES(1, 'hoge');
INSERT INTO people VALUES(2, 'fuga');
INSERT INTO people VALUES(3, 'foo');
INSERT INTO people VALUES(4, 'bar');
CREATE TABLE tel(
id SERIAL,
people_id INT,
tel TEXT
);
INSERT INTO tel VALUES(1, 1, '090-000-1111');
INSERT INTO tel VALUES(2, 1, '090-000-2222');
INSERT INTO tel VALUES(3, 2, '090-000-3333');
INSERT INTO tel VALUES(4, 3, '090-000-4444');
sql
WITH concat_tel_table AS ( -- 1で作成したSQL
SELECT
p.id,
ARRAY_AGG(t.tel) AS concat_tel
FROM people p
LEFT JOIN tel t
on p.id = t.people_id
GROUP BY p.id
)
SELECT
ctt.id,
ctt.concat_tel[1] AS tel1,
ctt.concat_tel[2] AS tel2
FROM concat_tel_table ctt