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のときと同様の結果が表示されるのが確認できました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

 埼玉県加須市にある、加須花崎水上公園の散歩道です。プールと自然観察園の間のほうの道で撮った写真になります。水上公園ということで、夏のプールが注目されますが、お花見やバーベキュー、また自然観察園ではいろいろな木々やウッドデッキ、野鳥の観察小屋などもあり、季節によっていろいろな表情が見れるので、私はとても気に入っています。

＜本日の題材＞
複数行のデータを集計して横展開

今回は、テーブルレイアウト上、キー毎に複数行あるデータを、横に並べて集計しながら1行で表示させたい場合のSQLについて取り上げてみたいと思います。（以前、カンマ区切りで横並びにするケースについて、SQL Serverの場合にFOR XML句を取り上げたことがありますが、今回はよく使う例としてCASE文を使用します）

例えば、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケース。(SQL Serverで検証)

DB上は以下のようにデータが登録されています。

SELECT
    部品コード
  , SUBSTRING(年月日,1,6) AS 年月
  , SUM(数量)  AS 仕入数量
  FROM 部品発注表
 GROUP BY 部品コード, SUBSTRING(年月日,1,6)
 ORDER BY 部品コード, SUBSTRING(年月日,1,6);

これを、部品コード毎で、月を横に並べて仕入数量を表示させたい場合、以下のようなSQLを実行します。

SELECT
  A.部品コード
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '04' THEN A.数量 ELSE 0 END) AS "4月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '05' THEN A.数量 ELSE 0 END) AS "5月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '06' THEN A.数量 ELSE 0 END) AS "6月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '07' THEN A.数量 ELSE 0 END) AS "7月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '08' THEN A.数量 ELSE 0 END) AS "8月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '09' THEN A.数量 ELSE 0 END) AS "9月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '10' THEN A.数量 ELSE 0 END) AS "10月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '11' THEN A.数量 ELSE 0 END) AS "11月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '12' THEN A.数量 ELSE 0 END) AS "12月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '01' THEN A.数量 ELSE 0 END) AS "1月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '02' THEN A.数量 ELSE 0 END) AS "2月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '03' THEN A.数量 ELSE 0 END) AS "3月"
  FROM [dbo].[部品発注表] A
  JOIN [dbo].[年度マスタ] B ON SUBSTRING(A.年月日,1,6) = B.年月
 WHERE B.年度 = '2014'
 GROUP BY A.部品コード
 ORDER BY A.部品コード;

このように、部品コード毎に、4月～3月まで月毎の仕入数量を横並びで表示させることができました。

上記では年度マスタを以下のように設定しています。
SELECT * FROM [dbo].[年度マスタ]
 ORDER BY 年度,表示順;

 ORACLEでも同様に行うことができますが、上記のSUBSTRING関数は、ORACLEではSUBSTR関数となります。そこだけ注意すれば同様に実行できます。

また、上記の例は、数量の場合でしたが、文字項目を横並びにしたい場合もあると思います。

例えば、下記のように、日本の様々なランキングをデータとして持つテーブルがあるとします。

SELECT
  A.区分NO
, B.区分名
, A.名称
, A.数値
, B.単位
  FROM dbo.ランキング A
  JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
  ORDER BY A.区分NO, A.NO;

※データはもっとたくさんありますが、表示は上記まで

これらの都市人口や川の長さ、湖の面積、山の高さなどの区分ごとに、ランキングの1位から10位までを横並びで表示させたいとします。

まず、各区分毎にランキング情報を抽出するのは、以下：

SELECT
  A.区分NO
, B.区分名
, A.名称
, RANK() OVER (PARTITION BY A.区分NO ORDER BY A.数値 DESC) RANK順位
, A.数値
, B.単位
  FROM dbo.ランキング A
  JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
  ORDER BY A.区分NO, RANK順位;

※データはもっとたくさんありますが、表示は上記まで

これを各区分毎に横並びで表示するのは、例えば以下のSQLのように、MAX関数などを使用して集約したかたちにします。

 SELECT
  C.区分NO
, C.区分名
, MAX(CASE C.RANK順位 WHEN 1 THEN C.名称 ELSE '' END)
  AS "1位"
, MAX(CASE C.RANK順位 WHEN 2 THEN C.名称 ELSE '' END)
  AS "2位"
, MAX(CASE C.RANK順位 WHEN 3 THEN C.名称 ELSE '' END)
  AS "3位"
, MAX(CASE C.RANK順位 WHEN 4 THEN C.名称 ELSE '' END)
  AS "4位"
, MAX(CASE C.RANK順位 WHEN 5 THEN C.名称 ELSE '' END)
 AS "5位"
, MAX(CASE C.RANK順位 WHEN 6 THEN C.名称 ELSE '' END)
  AS "6位"
, MAX(CASE C.RANK順位 WHEN 7 THEN C.名称 ELSE '' END)
  AS "7位"
, MAX(CASE C.RANK順位 WHEN 8 THEN C.名称 ELSE '' END)
  AS "8位"
, MAX(CASE C.RANK順位 WHEN 9 THEN C.名称 ELSE '' END)
  AS "9位"
, MAX(CASE C.RANK順位 WHEN 10 THEN C.名称 ELSE '' END)
  AS "10位"
 FROM
 (SELECT
   A.区分NO
 , B.区分名
 , A.名称
 , RANK() OVER (PARTITION BY A.区分NO ORDER BY A.数値 DESC) RANK順位
 , A.数値
    FROM dbo.ランキング A
    JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
 ) C
 GROUP BY C.区分NO, C.区分名
 ORDER BY C.区分NO;

 ※最近の統計では、最上川と木曽川はどちらも229kmで同じ7位となっているようですが、以前は最上川が7位、木曽川が8位とされていたようです。今回は、取りあえずそちらを使っています。

上記は、ORACLEでもまったく同様に実行することができます。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

写真は、前回と同じく「古代蓮の里」で撮った古代蓮の花です。
今年も暑い夏が続くと思えば、大型台風の影響で大雨などもあり、とても変わりやすく難しい天候ですね。台風などでこれ以上被害が出ないことを祈りつつ、天気予報をいつもチェックして注意しないといけないですね。

＜本日の題材＞
縦に並んだ複数行のデータをカンマ区切りで横並びに表示する方法(SQL Server)

縦に並んだ複数行のデータを、カンマ区切りで横並びに表示する方法について、最近SQL Serverで確認したので、忘れないようにアップしておきます。
SQL Serverでは、SQLクエリにFOR XML句というものを使うことで、SQLクエリの結果をXML形式で取得することができるのですが、今回はそれを使ってみる方法です。

例えば、地方とその地方に属する都道府県を、それぞれ、地域マスタ、府県マスタということで、下記のように持たせてみます。

SELECT * FROM dbo.地域マスタ
ORDER BY 地域CD;

SELECT * FROM dbo.府県マスタ
ORDER BY 府県CD;

… 続きは省略

ここで、上記の地域ごとの都道府県の情報を、カンマ区切りで横並びに表示させたいときに、以下のようにすると可能です。

SELECT
A.地域CD
, A.地域名
,(SELECT都道府県名 + ','
FROM dbo.府県マスタ
WHERE 地域CD = A.地域CD
ORDER BY 府県CD
FOR XML PATH('')) AS 都道府県名
FROM dbo.地域マスタ A
ORDER BY A.地域CD
;

FOR XML句をPATHモードで指定し、スペース区切りの文字列を生成しながら、カンマでつなげるというかたちにすると、上記の結果のようになります。
今日は以上まで

にほんブログ村