MW211 EXIT

devlog
Oracle/年月表(年度版)
2018年02月06日
年度版(4月~翌3月)。
┌──────────────────────────────────────┐
│WITH "年月D" AS (                                                          │
│    SELECT "年",                                                            │
│           "月",                                                            │
│           COUNT(*) AS "値"                                                 │
│        FROM (                                                              │
│            SELECT "年"                                                     │
│                   "月"                                                     │
│                FROM "表"                                                   │
│        )                                                                   │
│        GROUP BY "年",                                                      │
│                 "月"                                                       │
│)                                                                           │
│SELECT *                                                                    │
│    FROM (                                                                  │
│        SELECT CASE                                                         │
│                 WHEN "カレンダM"."月" < 4 THEN "カレンダM"."年" - 1      │
│                 ELSE                            "カレンダM"."年"          │
│               END AS "年度",                                               │
│               CASE                                                         │
│                 WHEN "カレンダM"."月" < 4 THEN "カレンダM"."月" + 12     │
│                 ELSE                            "カレンダM"."月"          │
│               END AS "月",                                                 │
│               NVL("年月D"."値", 0) AS "値"                                │
│            FROM (                                                          │
│                SELECT "年"."年",                                           │
│                       "月"."月"                                            │
│                    FROM (                                                  │
│                        SELECT 開始年度 + ROWNUM - 1  AS "年"               │
│                            FROM DUAL                                       │
│                                CONNECT BY ROWNUM <= 終了年度 - 開始年度 + 2│
│                    ) "年"                                                  │
│                        CROSS JOIN (                                        │
│                            SELECT ROWNUM AS "月"                           │
│                                FROM DUAL CONNECT BY ROWNUM <= 12           │
│                        ) "月"                                              │
│            ) "カレンダM"                                                  │
│                LEFT JOIN "年月D"                                          │
│                  ON  "年月D"."年" = "カレンダM"."年"                     │
│                  AND "年月D"."月" = "カレンダM"."月"                     
│    ) "集計D"                                                              │
│     PIVOT (                                                                │
│        SUM("値") FOR "月" IN ( 4 AS  "4月",                                │
│                                5 AS  "5月",                                │
│                                6 AS  "6月",                                │
│                                7 AS  "7月",                                │
│                                8 AS  "8月",                                │
│                                9 AS  "9月",                                │
│                               10 AS "10月",                                │
│                               11 AS "11月",                                │
│                               12 AS "12月",                                │
│                               13 AS  "1月",                                │
│                               14 AS  "2月",                                │
│                               15 AS  "3月")                                │
│    ) "ピボット"                                                            │
│    WHERE "年度" BETWEEN 開始年度 AND 終了年度                              │
│    ORDER BY "年度" ASC;                                                    │
└──────────────────────────────────────┘
  ・「開始年度」と「終了年度」の部分はそれぞれの数値に置き換える
  ・「年月D」の中身を変えれば、元データをいろいろなものにできる
分類:Oracle