SQL, MYSQLでピボットテーブル（クエリー）の作成、行を列に変えたいです。

Question

こんにちは。

在庫テーブルから入庫、出庫をピボットで出力し、日付のグループで小計を計算しようと思っております。

・テーブルの構造は下のリンクからご覧ください。

http://sqlfiddle.com/#!9/76c479/1/0

→
・typeが１の場合、入庫を
・typeが２の場合、出庫を出力します。
・在庫は入庫ー出庫の計算です。

① 変更前

> SELECT date, goods, type, value FROM container ORDER BY date, goods;

date          goods         type        value
日付	      商品	    タイプ	type1:入庫、type2:出庫
2015/10/1	牛乳パック	  1	        50
2015/10/1	牛乳パック	  2	        30
2015/10/1	牛乳パック	  2	        20
2015/10/1	牛乳パック	  1	        100
2015/10/1	牛乳パック	  2	        70
2015/10/1	弁当	        1	        20
2015/10/1	弁当	        1	        20
2015/10/1	弁当	        2	        20
2015/10/1	弁当	        1	        50
2015/10/1	弁当	        2	        30
2015/10/1	パン	        1	        100
2015/10/1	パン	        2	        80
2015/10/1	パン	        2	        20
2015/10/1	パン	        1	        30
2015/10/1	パン	        2	        20
2015/10/2 ........
…………..				

②変更後 ← 期待している形式です。

> SELECT o.date、o.goods、（SELECT SUM(t1.value) from container as t1 where t1.goods = o.goods and t1.type=1group by t1.goods ） as '入庫', （select SUM(t2.value) from container as t1 where t2.goods = o.goods AND t2.type=2 group by t2.goods ） as '出庫', '入庫' - '出庫' as '在庫' from container as o group by o.goods, o.date order by o.date, o.goods ← このクエリーはサンプルで、実際動作できませんでした。

日付	     商品	     入庫	出庫	在庫
2015/10/1	牛乳パック	150	120	30
2015/10/1	弁当	      90	50	40
2015/10/1	パン	      130	120	10
小計		               370	290	80
2015/10/2	牛乳パック	150	120	30
2015/10/2	弁当	        90	50	40
2015/10/2	パン	        130	120	10
小計		                370	290	80
2015/10/3	牛乳パック	150	120	30
2015/10/3	弁当	        90	50	40
2015/10/3	パン	       130	120	10
小計		               370	290	80
合計                          xxx xxx  xxx

…………..				

→要は入庫、出庫をピボットで出力させ、日付と商品でグループしたいです。
＊小計と合計はクエリーで出力するのもいいですが、viewのhtmlページで実装するのもいいと思います。

上のリンクからクエリーの試しが可能ですので、よろしくお願いいたします。

Answer 1

SQL1つでむりくり実行するなら、こんな感じでしょうか？

SELECT *
FROM (
	SELECT date, goods as '商品'
		, SUM(CASE WHEN type = 1 THEN value ELSE 0 END) as '入庫'
		, SUM(CASE WHEN type = 2 THEN value ELSE 0 END) as '出庫'
		, SUM(CASE WHEN type = 1 THEN value ELSE value*(-1) END) as '在庫'
	FROM container
	GROUP BY date,goods
	
	UNION
	
	SELECT date, '_小計' as '商品'
		, SUM(CASE WHEN type = 1 THEN value ELSE 0 END) as '入庫'
		, SUM(CASE WHEN type = 2 THEN value ELSE 0 END) as '出庫'
		, SUM(CASE WHEN type = 1 THEN value ELSE value*(-1) END) as '在庫'
	FROM container
	GROUP BY date
	
	UNION
	
	SELECT null as date, '合計' as '商品'
		, SUM(CASE WHEN type = 1 THEN value ELSE 0 END) as '入庫'
		, SUM(CASE WHEN type = 2 THEN value ELSE 0 END) as '出庫'
		, SUM(CASE WHEN type = 1 THEN value ELSE value*(-1) END) as '在庫'
	FROM container
) data
ORDER BY data.date,data.商品 desc

もし絞り込み条件が増えるとコードが増えて煩雑になります。
PHP等で加工して出力するなら、在庫と小計と合計はそっちで算出したほうがいいかも知れませんね。

Comments

[super]

完璧に解決ができました。凄く勉強になりました。
ありがとうございます。

Answer 2

要望の出力を得るのは関係代数演算では不可能かと思います。
ご要望のことは、ロール的に考えてもViewなので、Viewでやるのが普通かと思います。