質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

3回答

12401閲覧

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

ms5025

総合スコア292

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/11/16 01:38

前提・実現したいこと

たとえば以下のように
本日分合計金額
内訳 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はどのように書くのが一番スマートでしょうか?

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

試したこと

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

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

sql

1create temp table test_tbl (sdate date, type varchar, amount int); 2insert into test_tbl values 3('20200101','A',100), 4('20200102','A',100), 5('20200103','A',100), 6('20200101','A',110), 7('20200103','A',110), 8('20200104','A',110), 9('20200102','B',100), 10('20200103','B',100), 11('20200101','B',110), 12('20200102','B',110), 13('20200103','B',110), 14('20200104','B',110), 15('20200101','C',100), 16('20200102','C',100), 17('20200101','C',110), 18('20200102','C',110); 19 20create temp table typ_id (id int, type varchar); 21insert into typ_id values 22(1,'A'),(2,'B'),(3,'C'); 23 24SELECT 25 sdate, 26 SUM(amt), 27 hstore_to_json(hstore(array_agg(type)::text[], array_agg(amt)::text[])) 28FROM (SELECT sdate, type, sum(amount) amt from test_tbl join typ_id USING(type) 29 GROUP BY type,sdate) tb1 30GROUP BY sdate ORDER BY sdate;

投稿2020/01/04 14:09

amura

総合スコア333

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

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

SQL

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

追記

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

SQL

1select date, sum(type_total) sub_total 2 , array_agg(type order by type) type_ary 3 , array_agg(type_total order by type) type_total_ary 4from ( 5 SELECT date, type, SUM(amount) AS type_total 6 FROM table t1 left join type_table t2 7 on t1.type_id=t2.type_id 8 GROUP BY date, type 9) step1 10group by date

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

投稿2019/11/16 10:05

編集2019/11/16 13:17
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ms5025

2019/11/16 11:45 編集

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

2019/11/16 13:00

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

2019/11/16 13:14

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

0

投稿2019/11/16 03:57

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問