SQLで縦横変換して取得する方法を教えてください。

Question

下記のテーブルを縦横変換して取得したいです。

personテーブル

no |name    |date    |weight
---+--------+--------+-------
1  |tanaka  |2015/4  |60
1  |tanaka  |2016/4  |62
2  |yamada  |2014/4  |61
2  |yamada  |2015/4  |66
2  |yamada  |2016/4  |64

↓こう取得したい

personテーブル

no |name    |2014/4  |2015/4  |2016/4
---+--------+--------+---------------
1  |tanaka  |        |60      |62
2  |yamada  |61      |66      |64

日付カラムの値をカラムに使用したいです。

決め打ちで指定してピボットをかける方法が紹介されている記事を拝見しました。
dateカラムに入ってくる値が固定であればその方法を使用できるのですが、
不定な値が入ってくるためあてはめることができませんでした。

どなたか方法をご存知の方教えていただけないでしょうか。

Answer 1

• プロシージャで処理する
• ピボットテーブルを任意に作成して処理する
• プログラムで一度日付データだけ抜き出し、SQL文を作成する
• 冗長なデータをプログラムで整形する

のいずれかが妥当です

sample

もし日付を先に抜き出して集計表を作る場合は以下のようになります

• 元データ

create table person(no int,d date,weight double);
insert into person values(1,'2015-04-01',60),(1,'2016-04-01',62),(2,'2014-04-01',61),(2,'2015-04-01',66),(2,'2016-04-01',64);

• 日付抜き出し

select distinct d from person order by d asc;

• 集計表

select no
,sum(weight*(d='2014-04-01')) as `2014-04-01`
,sum(weight*(d='2015-04-01')) as `2015-04-01`
,sum(weight*(d='2016-04-01')) as `2016-04-01`
from person
group by no

※sum・・・の箇所をプログラム吐き出します

Comments

[退会済みユーザー]

日付だけ抜き出してみます。

[退会済みユーザー]

教えていただいたサンプルで無事動きました。

当初は「sum要らないじゃん」と思っていたのですが、なるほどでした。
ありがとうございます。

Answer 2

ＳＱＬで縦データを横に変換する方法はないですよ。

日付ごとに単純に処理をしたいのであれば、
日付でソートしてその結果をループするのが良いと思います。
（ブレイク条件を日付にする）

Comments

[退会済みユーザー]

そんな。。

[himakuma]

自分も同じ状態に何度もあっていますが、エクセルの入れ替えみたいなことはできないですorz。現状のデータでどう処理するかを考えたほうが良いですね。画面の表示であればループの方式を考えればいけますよ。

[himakuma]

日付ごとにＳＱＬを発行するのだけは回避してください。性能がひどくなります。

[退会済みユーザー]

sqlを二回に分けてみます。一旦日付たちを取得すれば決め打ちでsqlを投げることができそうだなと。

[himakuma]

GROUP BYで日付を取得して、ＳＱＬ生成という感じですかね？
それであれば日付にインデックスをつけることをお勧めします。

[退会済みユーザー]

インデックスについて知識が全くありません。。もう少し勉強します。
ありがとうございます。

[himakuma]

entityやbeanを使用して、オブジェクトをレコードとして受け取るような実装をしている場合、動的にすることでより、メンドクサイことにならないように気をつけてください。リフレクションするぐらいなら動的にしない方がいいです。

[himakuma]

PHPとかの型が固定でない場合は問題ないですが。。。