SQLの行固定出力について

Question

表題の件が分からないため教えて下さい。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

店テーブル
店コード 店名
1 A店
2 B店

売上テーブル
年度 店コード C商品売上高 C商品回収高 D商品売上高 D商品回収高
2015 1 5000 9000 5000 80000
2015 2 8000 12000 1000 30000
2016 1 7000 11000 6000 10000
2016 2 7000 13000 5000 10000

上記のテーブルを結合した場合の結果が、

商品 店コード 2015年度売上 2015年度回収 2016年度売上 2016年度回収
C商品 1 5000 9000 7000 11000
D商品 1 5000 8000 6000 10000

上記のように、一つの店に絞っての摘出を考えております。
商品名の行固定の名前の付け方や、列管理していたデータを行で表示する方法がわかりません。

ご教示の程、よろしくお願い致します。

Answer 1

お求めの表は SQL でできないことはないと思いますが、労力に見合わないと思います。
素直に別の言語を使った方が良さそうです。

また、それ以前の問題として、テーブルの設計が悪い気がします。まるで Excel のようです。
商品が増える度にテーブルが横に伸びていくのはとても気持ち悪いです。
例えば次のようなテーブル構造だったとしたらもっと扱いやすく、集計も楽になると思います。

店コード	店名
1	A 店
2	B 店

商品コード	商品名
3	商品 C
4	商品 D

年度	店コード	商品コード	売上	回収
2015	1	3	5000	9000
2015	1	4	5000	80000
2015	2	3	8000	12000
2015	2	4	1000	30000
2016	1	3	7000	11000
2016	1	4	6000	10000
2016	2	3	7000	13000
2016	2	4	5000	10000

Comments

[Orlofsky]

時々第一正規化さえなっていないシステムの改善を求められて、今のシステムを捨てて作り直した方が安上がりです、って言わざるを得ないことがあります。質問者が勉強環境でお試しで作ったテーブルであることを願います。

[duster]

今回は勉強目的で作成したテーブルです。
確かに、ご回答頂いたテーブルの方がイメージがつきやすく摘出しやすいと思いました。
ご回答頂きありがとうございます。

Answer 2

こうですかね？

• 元データ

create table uriage(y int,shop int,c1 int,c2 int,d1 int,d2 int);
insert into uriage values
(2015,1,5000,9000,5000,80000),
(2015,2,8000,12000,1000,30000),
(2016,1,7000,11000,6000,10000),
(2016,2,7000,13000,5000,10000);

• 集計

select item,shop
,sum((y=2015)*uriage) as u2015
,sum((y=2015)*kaishu) as k2015
,sum((y=2016)*uriage) as u2016
,sum((y=2016)*kaishu) as k2016
from (
select y,shop,'c' as item,c1 as uriage,c2 as kaishu from uriage
union all select y,shop,'d',d1,d2 from uriage
) as temp
where shop=1
group by item,shop;

Comments

[duster]

ご回答頂きありがとうございます。
確かに元データがそのようなら、SQLも短くなり分かりやすく思います。
そのような摘出方法も勉強になりました。

Answer 3

元のテーブルを前提にすると、おそらくこんな感じのSQLにするしかないです（未検証なので間違ってるかもしれないけど）。店テーブルはデータが欲しい結果に含まれていないため使用していません。

SELECT
	'C商品' AS 商品
	,S2015.店コード
	,S2015.C商品売上高 AS 2015年度売上
	,S2015.C商品回収高 AS 2015年度回収
	,S2016.C商品売上高 AS 2016年度売上
	,S2016.C商品回収高 AS 2016年度回収
FROM
	(SELECT 店コード, C商品売上高 ,C商品回収高 FROM 売上テーブル
		WHERE 年度 = 2015) AS S2015
	INNER JOIN
	(SELECT 店コード, C商品売上高 ,C商品回収高 FROM 売上テーブル
		WHERE 年度 = 2016) AS S2016
	ON S2015.店コード = S2016.店コード
WHERE
	店コード = 1

UNION ALL

SELECT
	'D商品' AS 商品
	,S2015.店コード
	,S2015.D商品売上高 AS 2015年度売上
	,S2015.D商品回収高 AS 2015年度回収
	,S2016.D商品売上高 AS 2016年度売上
	,S2016.D商品回収高 AS 2016年度回収
FROM
	(SELECT 店コード, D商品売上高 ,D商品回収高 FROM 売上テーブル
		WHERE 年度 = 2015) AS S2015
	INNER JOIN
	(SELECT 店コード, D商品売上高 ,D商品回収高 FROM 売上テーブル
		WHERE 年度 = 2016) AS S2016
	ON S2015.店コード = S2016.店コード
WHERE
	店コード = 1

複雑なうえ、これでは年度や商品が変わる度にSQLを見直す必要がありメンテナンス性が非常に悪いです。テーブル自体も毎年カラムの追加が必要でしょう。問題はテーブルが正規化されていないことです。可能であれば売上テーブルを以下のように変更してください。

年度	店コード	商品	売上高	回収高
2015	1	C商品	5000	9000
2015	1	D商品	5000	80000
2015	2	C商品	8000	12000
2015	2	D商品	1000	30000
2016	1	C商品	7000	11000
2016	1	D商品	6000	10000
2016	2	C商品	7000	13000
2016	2	D商品	5000	10000

欲しい結果自体が非正規形のため、まだ条件やカラム名を年度ごとに指定する必要がありますが、これでだいぶまともなSQLになります。CASE式と集約関数の利用がポイントです。

SELECT
	商品
	,店コード
	,MAX(CASE WHEN 年度 = 2015 THEN 売上高 ELSE 0 END) AS 2015年度売上
	,MAX(CASE WHEN 年度 = 2015 THEN 回収高 ELSE 0 END) AS 2015年度回収
	,MAX(CASE WHEN 年度 = 2016 THEN 売上高 ELSE 0 END) AS 2016年度売上
	,MAX(CASE WHEN 年度 = 2016 THEN 回収高 ELSE 0 END) AS 2016年度回収
FROM
	売上テーブル
GROUP BY
	商品
	,店コード
WHERE
	店コード = 1

正規化の技術を身に着けてください。

Comments

[duster]

ご回答頂きありがとうございます。
元テーブルの内容を反映させて頂き無事摘出することが出来ました。
正規化の例も頂きありがとうございます。
確かに商品は横でなく縦で管理すべきでした。

Answer 4

欲しい結果が正しくないのではありませんか？
意図しているのは「ある商品の、ある店での売り上げの、年度別売り上げと回収の並び」ですよね。
であるならば結果は

商品	店コード	2015年度売上	2015年度回収	2016年度売上	2016年度回収
C商品	1	5000	9000	7000	11000
C商品	2	8000	12000	7000	13000
D商品	1	5000	80000	6000	10000
D商品	2	1000	30000	5000	10000

であってほしいのでは？

Comments

[duster]

ご指摘ありがとうございます。
取得イメージが違いました。

商品 店コード 2015年度売上 2015年度回収 2016年度売上 2016年度回収
C商品 1 5000 9000 7000 11000
D商品 1 5000 8000 6000 10000

上記が正しいデータイメージです。