別テーブルを参照して縦持ちのデータを動的に横持ちにする方法

Question

前提・実現したいこと

たとえば以下のように
本日分合計金額
内訳　A　＠＠円
内訳　B　＠＠円
内訳　C　＠＠円

といったSQLがあるとします

  SELECT
    SUM(amount) AS sub_total
    ,SUM(case when type = 'A' then amount else 0 end) AS A_sub_total
    ,SUM(case when type = 'B' then amount else 0 end) AS B_sub_total
    ,SUM(case when type = 'C' then amount else 0 end) AS C_sub_total
  FROM table 
  GROUP BY date

固定で決まっていたABCがid,typeで別テーブルで管理されることになりました。
元のテーブルにはtypeの代わりにtype_idが付与されます。
これにより内訳が可変となります。
期待のデータを横持ちの形で取得するSQLはどのように書くのが一番スマートでしょうか？

ペラ１のsqlで
日付、合計金額、A＠＠円、B＠＠円、・・・（以下TYPEテーブル分可変）と、
一行で取得したいです。

試したこと

新しくできたTypeテーブルをループして
caseの部分を動的で組む、といったことしか思いつかないのですが
固定SQLで実現可能でしょうか？

Answer 1

JSON型にて取り出します。
hstore型（ハッシュ？）にarray_agg()で追加して列を作成しました。 ご参考まで

create temp table test_tbl (sdate date, type varchar, amount int);
insert into test_tbl values
('20200101','A',100),
('20200102','A',100),
('20200103','A',100),
('20200101','A',110),
('20200103','A',110),
('20200104','A',110),
('20200102','B',100),
('20200103','B',100),
('20200101','B',110),
('20200102','B',110),
('20200103','B',110),
('20200104','B',110),
('20200101','C',100),
('20200102','C',100),
('20200101','C',110),
('20200102','C',110);

create temp table typ_id (id int, type varchar);
insert into typ_id values
(1,'A'),(2,'B'),(3,'C');

SELECT
 sdate,
 SUM(amt),
 hstore_to_json(hstore(array_agg(type)::text[], array_agg(amt)::text[]))
FROM (SELECT sdate, type, sum(amount) amt from test_tbl join typ_id USING(type)
      GROUP BY type,sdate) tb1
GROUP BY sdate ORDER BY sdate;

Answer 2

分割されたものを結合して元のテーブルをfrom句内で再現してやれば、from句以外は元のままでイケます。

SELECT
     SUM(amount) AS sub_total
    ,SUM(case when type = 'A' then amount else 0 end) AS A_sub_total
    ,SUM(case when type = 'B' then amount else 0 end) AS B_sub_total
    ,SUM(case when type = 'C' then amount else 0 end) AS C_sub_total
FROM table t1 left join type_table t2
     on t1.type_id=t2.id
GROUP BY date

追記

下記で、type_listにtypeが列挙され、それと同じ並びでtype毎の計が列挙されます。

select date, sum(type_total) sub_total
     , array_agg(type order by type) type_ary
     , array_agg(type_total order by type) type_total_ary
from (
  SELECT date, type, SUM(amount) AS type_total
  FROM table t1 left join type_table t2
       on t1.type_id=t2.type_id
  GROUP BY date, type
) step1
group by date

後は受け取るプログラム側で、typeテーブルで列の数を決めて、該当する箇所に埋め込むようにします。

Comments

[ms5025]

この　case when type = 'A'　の所が固定ではなく、
type テーブルの値になり、可変になります。
固定で書けるなら今のままでいいのですが。
最悪A-Dになるかもしれないし、　A-JJになるかもしれません。
一つ一つcase文を書くわけに行かず、
個人的にはタイプテーブルでループして動的SQL作るしか思いつきません

[sazi]

SQLの結果の列を可変にするなら、動的SQLしかありません。
ですが、動的SQLで問合せしたとして、それを受ける側のプログラムはどうするのですか？

[sazi]

受け取りのプログラムもtypeテーブルに登録されている数分の列が返ってくるのを期待しているという事なら可能だと思います。
列を固定にするなら配列になるかと思いますので、例を追記しておきました。

Answer 3

PostgreSQL 9.0+ STRING_AGGで順序を指定する で横に並べては？