SQL Server の PIVOT / UNPIVOT で業務帳票のクロス集計表を作る

みなさんこんにちは！ヒロポンです！

「月ごとの売上を、商品を縦・月を横にした表で出してほしい」。業務系だと、この手の帳票依頼、しょっちゅう来ますよね？？

データは縦持ち（1行1レコード）で持ってる。なのに帳票は横持ちで、月が列にずらっと並ぶ。この縦→横の変換、GROUP BY と CASE を延々並べて手書きしてる人、けっこう多いはずです。

SQL Server には、これを一発でやる PIVOT があります。今回は SQL Server の PIVOT の使い方を、売上テーブルのクロス集計を例にたどっていきます。横持ちを縦持ちに戻す UNPIVOT、それに列が増える時の動的 SQL まで、コピペで動く形で潰しましょう。

結論: 縦→横は PIVOT、横→縦は UNPIVOT

先に結論から。

• 縦持ち → 横持ち（帳票のクロス集計）は PIVOT
• 横持ち → 縦持ち（正規化された形に戻す）は UNPIVOT

PIVOT は、指定した列の値を「列そのもの」に展開して、値を集計してくれる T-SQL の演算子です。イメージはこんな感じ。

実際のテーブルで見ていきます。

サンプルの売上テーブル（縦持ち）

商品×月の売上を縦持ちで持ってる、こういうテーブルを使います。

CREATE TABLE Sales (
    Product   NVARCHAR(20),
    SaleMonth INT,
    Amount    INT
);

INSERT INTO Sales (Product, SaleMonth, Amount) VALUES
(N'りんご', 1, 100), (N'りんご', 2, 120), (N'りんご', 3, 150),
(N'みかん', 1,  80), (N'みかん', 2,  90), (N'みかん', 3,  70),
(N'ぶどう', 1, 200), (N'ぶどう', 2, 210), (N'ぶどう', 3, 190);

9行、縦に並んでます。これを「商品を行、月を列」の帳票にしていきます。

ひとつ寄り道すると、日本語を NVARCHAR の列に入れる時は、文字列の頭に N を付けて N'りんご' と書きます。忘れると、DB の照合順序によっては日本語が ? に化けて格納される。地味ですけど、けっこう大事なやつです。

PIVOT で縦持ちを横持ちのクロス集計表にする

PIVOT を使うとこんな感じで書けます。

SELECT Product AS 商品, [1] AS [1月], [2] AS [2月], [3] AS [3月]
FROM (
    SELECT Product, SaleMonth, Amount FROM Sales
) AS src
PIVOT (
    SUM(Amount) FOR SaleMonth IN ([1], [2], [3])
) AS pvt
ORDER BY 商品;

結果はこうなります。

商品     1月   2月   3月
------   ----  ----  ----
ぶどう   200   210   190
みかん    80    90    70
りんご   100   120   150

実際に SQL Server で実行した結果がこれです。

縦に9行あったデータが、商品ごと3行の横持ちに変わりました。いい感じにクロス集計できてます。

ポイントは3つ。

• SUM(Amount) … 集計する値と集計関数
• FOR SaleMonth … どの列の値を列に展開するか
• IN ([1], [2], [3]) … 展開する値（＝新しい列名）を列挙する

この IN (...) に「1月, 2月, 3月」を手で書いてる。ここが、あとで効いてきます。

PIVOT は列を静的に列挙しないといけない（動的 PIVOT）

さっきの IN ([1], [2], [3])。ここ、実行時に列を決められません。SQL を書く時点で「どの月を列にするか」を固定で書く必要があります。

ん？ 月が増えたらどうすんの？？って引っかかりますよね。4月、5月とデータが増えても、IN (...) に手で足さない限り列は出てきません。

業務でいちばん最初に詰まるのが、まさにここ。対処は、列リストを文字列で組み立てて、動的 SQL で実行するやり方です。

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 実在する月を [1],[2],[3] の形で組み立てる（SQL Server 2016 でも動く）
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(SaleMonth)
    FROM Sales
    ORDER BY ',' + QUOTENAME(SaleMonth)
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @sql = N'
SELECT Product AS 商品, ' + @cols + N'
FROM (SELECT Product, SaleMonth, Amount FROM Sales) AS src
PIVOT (SUM(Amount) FOR SaleMonth IN (' + @cols + N')) AS pvt
ORDER BY 商品;';

EXEC sp_executesql @sql;

これなら、月が増えても勝手に列が増えます。STUFF + FOR XML PATH は列名を1つの文字列に連結する定番のイディオムで、SQL Server 2016 でも動きます。

SQL Server 2017 以降なら、@cols の組み立ては STRING_AGG(QUOTENAME(SaleMonth), ',') でもっと短く書けます。現場のバージョンに合わせてください。

GROUP BY + CASE で手書きする方法

PIVOT を使わず、GROUP BY と CASE で同じ表を作ることもできます。というか、昔からあるのはこっちの書き方。

SELECT Product AS 商品,
       SUM(CASE WHEN SaleMonth = 1 THEN Amount ELSE 0 END) AS [1月],
       SUM(CASE WHEN SaleMonth = 2 THEN Amount ELSE 0 END) AS [2月],
       SUM(CASE WHEN SaleMonth = 3 THEN Amount ELSE 0 END) AS [3月]
FROM Sales
GROUP BY Product
ORDER BY Product;

結果は PIVOT 版とまったく同じ。冗長に見えますけど、こっちにも利点があります。

CASE 版は「1月の売上」と「1月の件数」みたいに、複数の集計を1クエリで並べたい時に強い。PIVOT は1回に1集計なので、そこは CASE の方が素直に書けます。まあ使い分けですね。

UNPIVOT で横持ちを縦持ちに戻す

逆に、横持ちの帳票テーブルを縦持ちに正規化したい時は UNPIVOT の出番です。

-- 横持ちの帳票テーブル（例）
CREATE TABLE CrossTab (
    商品 NVARCHAR(20), [1月] INT, [2月] INT, [3月] INT
);
INSERT INTO CrossTab (商品, [1月], [2月], [3月]) VALUES
(N'りんご', 100, 120, 150),
(N'みかん',  80,  90,  70);

SELECT 商品, 月, 売上
FROM CrossTab
UNPIVOT (
    売上 FOR 月 IN ([1月], [2月], [3月])
) AS unpvt
ORDER BY 商品, 月;