複数行返ってくるサブクエリを文字列に連結して取得するSQLまとめ
SELECT name FROM TABLE_NAME WHERE id IN(1, 2, 3)
みたいな複数行の結果セットが返ってくるクエリを、
複数行を結合した文字列にしつつ、サブクエリとして扱って、
SELECT column1, column2, (SELECT name FROM TABLE_NAME WHERE id IN(1, 2, 3)) AS names FROM ...
みたいにスバッと書きたいみたいなことが、たまにあります。
文字列への結合をプログラム側でやらなくて済むと嬉しいケースもあるし。
ただし、これはRDBMSによって、書き方が結構異なります。
なんで、アレっちゃアレなんですが、まとめてみました。
対象RDBMSはPostgreSQL、MySQL、Oracle。（というか他は知らない。。。）
前提
SELECT name FROM TABLE_NAME WHERE id IN(1, 2, 3)
は
| name | name1 name2 name3
という結果セットを返すものとします。
説明のため。
PostgreSQL
ARRAY_TO_STRING(ARRAY(SELECT name FROM TABLE_NAME WHERE id IN(1, 2, 3)), ',')
ポスグレは ARRAY() で複数行結果セットを配列として扱うことができるので、
一旦配列にして、 ARRAY_TO_STRING() で文字列に結合します。
ARRAY_TO_STRING() の第二引数が結合文字列。
よって、これを1カラムとしてなげると、
name1,name2,name3
とゆー文字列が返ってきます。
MySQL
SELECT GROUP_CONCAT(name SEPARATOR ',') FROM TABLE_NAME WHERE id IN(1, 2, 3)
これは割と有名っぽいので、GROUP_CONCAT でぐぐるといっぱいでてきますね。
ただポスグレと理屈は違って、GROUP BY で集約したものを結合(CONCAT)するものなので、
他に条件や集約がついている場合は、SQLをごにょごにょと辻褄合わせる必要があります。
結果は上に同じなので略。
さらに、GROUP_CONCAT() は ORDER BY を指定でき、DISTINCTもつけられる。
SELECT GROUP_CONCAT(DISTINCT name ORDER BY id DESC SEPARATOR ',') FROM TABLE_NAME WHERE id IN(1, 2, 3)
Oracle
SELECT REGEXP_REPLACE(REPLACE(XMLAGG(XMLELEMENT("elm", name)), '</elm><elm>', ','), '(<elm>|</elm>)')
FROM TABLE_NAME WHERE id IN(1, 2, 3)結構自信無い上に、いろんなやり方があるっぽいですが、
以前かじる程度に使ったのはこんな感じ。
複数行を返すカラムを XMLELEMENT() でXMLエレメントにする。
（XMLELEMENT() の第一引数はエレメントの名前ですが、ダブルクォートで指定しないとダメっぽい）
そして、XMLAGG() で全エレメントを文字列に変換。
変換した文字列を、REPLACE系関数を使って、頑張っていらない部分やらを何とかする。
無理矢理感にあふれていて申し訳ないです。。
それにXML系関数の使えないバージョンのOracleだともちろんできません。
結果は "一応" 上に同じなので略。
で、"一応"と書いたのは、上のやり方だと、
name の内容はXML向けに一部の文字が実体参照に変換されます。
[<] → < ['] → ' などなど
これにより、想定外の挙動が起こりうるので注意が必要です。
以上、PostgreSQL、MySQL、Oracleでまとめてみました。
確かに、アレっちゃアレって感じなのが伝わってしまったかもしれませんが、
何かの一助になれば幸いです。
っていうか間違ってたら容赦無く突っ込んであげて下さい＞＜
