横持ちデータを縦持ちへ変換するSQL
目的
BIなどでグラフを書く場合、データが横持になっているとうまくグラフ化できない、といったケースがあります。ということでSQLでデータを横持から縦持ちに変換する方法について試します。
環境
PostgreSQL 11.5
使用データ
以下のように、年だけ縦持ちで月が横持ちになっている残高データを例にします。各月のフィールドの中には金額データが格納されています。
postgres=# select * from test_kingaku_yoko;
年 | １月 | ２月 | ３月 | ４月 | ５月 | ６月 | ７月 | ８月 | ９月 | １０月 | １１月 | １２月
------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------
2017 | 222400 | 237900 | 244100 | 208400 | 237100 | 208700 | 210600 | 291300 | 256200 | 232200 | 240000 | 204000
2018 | 241500 | 226300 | 211900 | 241000 | 264800 | 207800 | 241700 | 278200 | 291900 | 241700 | 204700 | 206500
2019 | 298500 | 254900 | 285400 | 214500 | 269700 | 247200 | 282800 | 270000 | 272400 | 226600 | 214600 | 236100上記のようなデータ構造の場合、BIでグラフ化しようとした場合に、横軸の選択に各月がバラバラに表れてしまうため、一度にすべての月のデータをグラフ化するのが難しかったりします。
縦持ちにするSQL
次のように各月のカラム毎にデータを抽出し、それらをunion allでつなげることでデータを縦持ちにすることができます。
select
年
,1 as 月
,１月 as 金額
from
test_kingaku_yoko
union all
select
年
,2 as 月
,２月 as 金額
from
test_kingaku_yoko
union all
select
年
,3 as 月
,３月 as 金額
from
test_kingaku_yoko
union all
select
年
,4 as 月
,４月 as 金額
from
test_kingaku_yoko
union all
select
年
,5 as 月
,５月 as 金額
from
test_kingaku_yoko
union all
select
年
,6 as 月
,６月 as 金額
from
test_kingaku_yoko
union all
select
年
,7 as 月
,７月 as 金額
from
test_kingaku_yoko
union all
select
年
,8 as 月
,８月 as 金額
from
test_kingaku_yoko
union all
select
年
,9 as 月
,９月 as 金額
from
test_kingaku_yoko
union all
select
年
,10 as 月
,１０月 as 金額
from
test_kingaku_yoko
union all
select
年
,11 as 月
,１１月 as 金額
from
test_kingaku_yoko
union all
select
年
,12 as 月
,１２月 as 金額
from
test_kingaku_yoko 年 | 月 | 金額
------+----+--------
2017 | 1 | 222400
2018 | 1 | 241500
2019 | 1 | 298500
2017 | 2 | 237900
2018 | 2 | 226300
2019 | 2 | 254900
2017 | 3 | 244100
2018 | 3 | 211900
2019 | 3 | 285400
2017 | 4 | 208400
2018 | 4 | 241000
2019 | 4 | 214500
2017 | 5 | 237100
2018 | 5 | 264800
2019 | 5 | 269700
2017 | 6 | 208700
2018 | 6 | 207800
2019 | 6 | 247200
2017 | 7 | 210600
2018 | 7 | 241700
2019 | 7 | 282800
2017 | 8 | 291300
2018 | 8 | 278200
2019 | 8 | 270000
2017 | 9 | 256200
2018 | 9 | 291900
2019 | 9 | 272400
2017 | 10 | 232200
2018 | 10 | 241700
2019 | 10 | 226600
2017 | 11 | 240000
2018 | 11 | 204700
2019 | 11 | 214600
2017 | 12 | 204000
2018 | 12 | 206500
2019 | 12 | 236100
(36 行)このような縦持ちの状態にするとBIでグラフ化する際、「月」という一列を横軸に持ってくることで、分かりやすいグラフを書くことができました。
