SQL をで行を横展開 - miau's blog?

このアイテム、blog の中でも参照率が高いやつなんですが。
・取得する列の数が限定されているので、本当の関連テーブルっぽいのには使いにくい
・sub_id みたいなのを持たないテーブルには使えない
・なんだかんだで、書くのが面倒
という欠点があります。

ただ単に見やすく表示したいだけであれば、PostgreSQL でもっと楽にできる方法もありますよ、って話。（多少難あり）

---

PostgreSQL では、集約関数を自分で定義できたりします。
これを作ってしまうと、

SELECT id, SUM(point) FROM Points GROUP BY id

みたいなノリで「カンマで文字列を結合する」集約処理

SELECT id, comma_join(point_name) FROM Points GROUP BY id

みたいなことができちゃいます。

では早速。

CREATE FUNCTION text_comma_join(text, text) RETURNS text AS '

	SELECT $1 || '','' || $2

' LANGUAGE SQL;



CREATE FUNCTION skip_initial(text) RETURNS text AS '

	SELECT substr($1, 2)

' LANGUAGE SQL;



CREATE AGGREGATE comma_join (

  BASETYPE = text,

  SFUNC = text_comma_join,

  STYPE = text,

  FINALFUNC = skip_initial,

  INITCOND = ''

);

こんな感じで定義。

すると、

# select * from customerfamily;

 cust_id | family_id |  name

---------+-----------+--------

       1 |         1 | 波平

       1 |         2 | フネ

       1 |         3 | カツオ

       1 |         4 | ワカメ

       2 |         1 | マスオ

       2 |         2 | サザエ

       2 |         3 | タラオ

(7 rows)

こんなテーブルのデータを・・・

# select cust_id, comma_join(name) from customerfamily group by cust_id;

 cust_id |       comma_join

---------+-------------------------

       1 | ワカメ,フネ,カツオ,波平

       2 | マスオ,サザエ,タラオ

(2 rows)

こんな感じで取得できますよ、と。

---

で、とまぁこんな感じで一見便利そうで、「なんで標準でこういう関数用意してないの？」と思えるくらいなんですが・・・当然のように問題もありまして。結果を見るとわかるように、表示順を保証する方法がありません。
そもそも集約関数内で交換不可能な演算子を使うのはルール違反という気がします。

ということで、あまりお薦めできる方法ではないですけど・・・軽くデータをチェックしたいような場合には便利なので一応紹介でした。