Hatena::Grouppostgresql

PostgreSQL 雑記 このページをアンテナに追加 RSSフィード

2011-04-16行を列に変換する このエントリーを含むブックマーク このエントリーのブックマークコメント

今度は行から列へ変換してみます。

=# CREATE TABLE vert AS
   SELECT
     id, seq, chr(ascii('A') + id - 1) || seq AS v
   FROM
     generate_series(1, 2) AS t(id),
     generate_series(1, 4) AS s(seq);
=# SELECT * FROM vert
 id | seq | v
----+-----+----
  1 |   1 | A1
  1 |   2 | A2
  1 |   3 | A3
  1 |   4 | A4
  2 |   1 | B1
  2 |   2 | B2
  2 |   3 | B3
  2 |   4 | B4
(8 rows)

generate_series() の汎用性は (ry

これを id 単位で行にまとめることを考えます。最終的な出力の型が先に決まっている必要があるため、各列の定義は手書きで展開しないといけません。以下の例では、min() は最小値が欲しいわけではなく、単に値を1つ取り出すために使っています。

=# SELECT
     id,
     min(CASE seq WHEN 1 THEN v END) AS v1,
     min(CASE seq WHEN 2 THEN v END) AS v2,
     min(CASE seq WHEN 3 THEN v END) AS v3,
     min(CASE seq WHEN 4 THEN v END) AS v4
   FROM vert GROUP BY id ORDER BY id;
 id | v1 | v2 | v3 | v4
----+----+----+----+----
  1 | A1 | A2 | A3 | A4
  2 | B1 | B2 | B3 | B4
(2 rows)

いちおう crosstabcontrib モジュールとして用意されているようですが、「SQLを文字列で与える」という仕様がイマイチ好きになれないので……。

列ではなく、集合型の1個の列で良ければ、もう少しすっきり書けます。例えば hstore に集約する場合には以下のような感じ。キーの値が予想できない場合は array_agg() or string_agg() を使うことになるでしょう。

=# SELECT
     id,
     hstore(array_agg(seq)::text[], array_agg(v))
   FROM vert GROUP BY id ORDER BY id;
 id |                   hstore
----+--------------------------------------------
  1 | "1"=>"A1", "2"=>"A2", "3"=>"A3", "4"=>"A4"
  2 | "1"=>"B1", "2"=>"B2", "3"=>"B3", "4"=>"B4"
(2 rows)

追記: よく考えたら array_agg() に ORDER BY 要らない。