ITコーディネータのシュウです。
新年あけましておめでとうございます。
写真は、今まで何回か写真をアップしている、加須はなさき水上公園の池のところを撮った写真です。池の水面に木々が写ってとてもきれいです。何気なく見ている景色も、見る角度や季節、時刻などが変わると、全然違うように感じることがありますね。
さて、新しい1年が出発しました。また１つ年を取ってしまったという思いもありますが、今年こそはやってやる！という夢と気概を持って、何事にも挑戦して行ければと思います。
本年も、よろしくお願いいたします。
＜本日の題材＞
横に並んだ項目を縦の行データに変換
以前、「複数行のデータを集計して横展開」という題名で、複数行の縦に並んだデータを、横に項目を並べて表示させるためのSQLを取り上げましたが、その反対に、最初から横に項目を並べて登録されているデータを、複数行の縦に並んだデータに変換するやり方については取り上げていませんでした。
実際のシステム開発において、そういうケースにも時折出会うことがあるため、今回一度取り上げてみたいと思います。
様々なシステムにおいて、縦には商品であったり、勘定科目であったり、そのシステムで管理したいデータを並べ、横の列に、例えば4月～翌年3月までの各月を項目として持たせて、年度毎の一覧を表示／修正させるようなケースがあると思います。そのときに、データベースのテーブル自体の列に、4月、5月、…12月、..3月というように、表示に合わせて、各月の項目を持たせるという設計になる場合もあります。
このようにして登録された横に並んだ各列のデータを、今度はひと月ずつのデータとして複数行に分けて処理したいという場合に、どのようなSQLにすればよいのか？ 以下に例を示します。
例）
商品ごとの各月の売上実績を登録するテーブルを以下のように定義、作成します。
CREATE TABLE dbo.商品売上(
商品CD VARCHAR(20)
, 年度 VARCHAR(4)
, 売上4月 DECIMAL(12)
, 売上5月 DECIMAL(12)
, 売上6月 DECIMAL(12)
, 売上7月 DECIMAL(12)
, 売上8月 DECIMAL(12)
, 売上9月 DECIMAL(12)
, 売上10月 DECIMAL(12)
, 売上11月 DECIMAL(12)
, 売上12月 DECIMAL(12)
, 売上1月 DECIMAL(12)
, 売上2月 DECIMAL(12)
, 売上3月 DECIMAL(12)
, CONSTRAINT PK_商品売上 PRIMARY KEY (商品CD, 年度))
;
データを以下のように登録します。
INSERT INTO dbo.商品売上('A0001', 2015, 495280, 503400, 485400, 534800, 521300, 494600, 538200, 482600, 546820, 483240, 452600, 517800);
INSERT INTO dbo.商品売上('B0001', 2015, 213300, 246400, 220480, 253100, 262300, 247200, 251800, 236700, 262400, 254300, 223800, 248900);
INSERT INTO dbo.商品売上('C0021', 2015, 165800, 139200, 181040, 166400, 176300, 168300, 192400, 168000, 201400, 187600, 176500, 194700);
このテーブルから、2015年度の商品CD毎の売上を普通に抽出すると
DECLARE
@年度 VARCHAR(4) = 2015
SELECT * FROM dbo.商品売上
WHERE 年度 = @年度
ORDER BY 商品CD;
このデータを、商品CD毎、一月毎の売上データとして、各月のデータを複数行に分けて抽出する場合、例えば以下のようにします。
DECLARE
@年度 VARCHAR(4) = 2015
SELECT SU.商品CD, SU.月, SU.売上
FROM
(SELECT 商品CD, 4 AS 月, ISNULL(売上4月,0) AS 売上, 1 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 5 AS 月, ISNULL(売上5月,0) AS 売上, 2 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 6 AS 月, ISNULL(売上6月,0) AS 売上, 3 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 7 AS 月, ISNULL(売上7月,0) AS 売上, 4 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 8 AS 月, ISNULL(売上8月,0) AS 売上, 5 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 9 AS 月, ISNULL(売上9月,0) AS 売上, 6 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 10 AS 月,ISNULL(売上10月,0) AS 売上, 7 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 11 AS 月,ISNULL(売上11月,0) AS 売上, 8 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 12 AS 月,ISNULL(売上12月,0) AS 売上, 9 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 1 AS 月, ISNULL(売上1月,0) AS 売上, 10 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 2 AS 月, ISNULL(売上2月,0) AS 売上, 11 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
UNION SELECT 商品CD, 3 AS 月, ISNULL(売上3月,0) AS 売上, 12 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
) SU
ORDER BY 商品CD, 表示順;
各月のデータを抽出し、UNIONで結合したものを1つのテーブルのようにみなして、そこからデータを抽出するというかたちです。
※結果を4月から順に上から並べたいので、表示順という項目をつけてみました。
上記はORACLEでも同様に行うことができます。
SELECT SU.商品CD, SU.月, SU.売上
FROM
(SELECT 商品CD, 4 AS 月, NVL(売上4月,0) AS 売上, 1 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 5 AS 月, NVL(売上5月,0) AS 売上, 2 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 6 AS 月, NVL(売上6月,0) AS 売上, 3 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 7 AS 月, NVL(売上7月,0) AS 売上, 4 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 8 AS 月, NVL(売上8月,0) AS 売上, 5 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 9 AS 月, NVL(売上9月,0) AS 売上, 6 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 10 AS 月,NVL(売上10月,0) AS 売上, 7 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 11 AS 月,NVL(売上11月,0) AS 売上, 8 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 12 AS 月,NVL(売上12月,0) AS 売上, 9 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 1 AS 月, NVL(売上1月,0) AS 売上, 10 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 2 AS 月, NVL(売上2月,0) AS 売上, 11 表示順 FROM 商品売上 WHERE 年度 = '2015'
UNION SELECT 商品CD, 3 AS 月, NVL(売上3月,0) AS 売上, 12 表示順 FROM 商品売上 WHERE 年度 = '2015'
) SU
ORDER BY 商品CD, 表示順;
SQL Serverのときと同様の結果が表示されるのが確認できました。
今日は以上まで
にほんブログ村