SQLのPIVOT句を使ったクロス集計をマスター: 行データを列データに変換するテクニック

SQLのPIVOT句は、データベース内の行データを列データに変換する際に欠かせないツールです。特に、レポート作成やデータの集計作業において、その価値は計り知れません。PIVOT句を使用する基本的なプロセスは、特定の列の値を行から列に変換し、その過程で集計関数を用いてデータを再構成することにあります。

PIVOTを使った便利な実例

PIVOTを使った便利な実例

月別販売集計

PIVOTを使用して月別の売上を集計します。MONTH(SaleDate)で月を抽出し、SUM(Amount)で月別売上を算出。結果は1月から12月までの売上が列として表示され、月ごとの売上トレンド分析に役立ちます。

Sales (販売データ)

SaleDate	Amount	CategoryID	EmployeeID	RegionID	Quantity
2023-01-10	200	1	1	1	2
2023-02-15	150	2	2	2	1
2023-03-20	300	3	1	3	3
…	…	…	…	…	…

SELECT *
FROM (
    SELECT
        MONTH(SaleDate) AS SaleMonth,
        Amount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR SaleMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS PivotTable;

SELECT *

FROM (

SELECT

MONTH(SaleDate) AS SaleMonth,

Amount

FROM Sales

) AS SourceTable

PIVOT (

SUM(Amount)

FOR SaleMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])

) AS PivotTable;

実行結果:

SaleMonth	1	2	3	…	12
合計	200	150	300	…	…

商品カテゴリ別売上高

商品カテゴリ別に売上高を集計する例です。JOINでCategoriesテーブルを結合し、PIVOTでCategoryNameごとの売上をSUM(Amount)で算出します。このSQLにより、各カテゴリの売上高が列に展開され、売れ筋カテゴリを把握できます。

Categories (商品カテゴリ)

CategoryID	CategoryName
1	Electronics
2	Clothing
3	Furniture
…	…

SELECT *
FROM (
    SELECT
        CategoryName,
        Amount
    FROM Sales
    JOIN Categories ON Sales.CategoryID = Categories.CategoryID
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR CategoryName IN ([Electronics], [Clothing], [Furniture])
) AS PivotTable;

SELECT *

FROM (

SELECT

CategoryName,

Amount

FROM Sales

JOIN Categories ON Sales.CategoryID = Categories.CategoryID

) AS SourceTable

PIVOT (

SUM(Amount)

FOR CategoryName IN ([Electronics], [Clothing], [Furniture])

) AS PivotTable;

実行結果:

CategoryName	Electronics	Clothing	Furniture
合計	200	150	300

年度別・四半期別売上集計

年度ごと、四半期ごとの売上を集計する例。YEAR(SaleDate)とCEILING(MONTH(SaleDate) / 3.0)で年度と四半期を算出し、PIVOTで四半期ごとの売上をSUM(Amount)で集計します。この方法で、四半期のパフォーマンスを年度別に分析できます。

SELECT *
FROM (
    SELECT
        YEAR(SaleDate) AS SaleYear,
        'Q' + CAST(CEILING(MONTH(SaleDate) / 3.0) AS VARCHAR) AS Quarter,
        Amount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable
ORDER BY SaleYear;

SELECT *

FROM (

SELECT

YEAR(SaleDate) AS SaleYear,

'Q' + CAST(CEILING(MONTH(SaleDate) / 3.0) AS VARCHAR) AS Quarter,

Amount

FROM Sales

) AS SourceTable

PIVOT (

SUM(Amount)

FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])

) AS PivotTable

ORDER BY SaleYear;

実行結果:

SaleYear	Q1	Q2	Q3	Q4
2023	25000	30000	35000	40000

従業員別売上目標達成率

従業員ごとの売上目標と実際の売上を比較し、達成率を計算する例。Employeesテーブルから目標を取り、Salesテーブルから実売上を集計し、PIVOTでTargetとActualを列として表示します。目標達成度を確認し、従業員のパフォーマンス評価に活用できます。

Employees (従業員)

EmployeeID	EmployeeName	SalesTarget
1	John Doe	5000
2	Jane Smith	6000
…	…	…

SELECT EmployeeName, [Target], [Actual]
FROM
(
    SELECT e.EmployeeName, e.SalesTarget AS Value, 'Target' AS Metric
    FROM Employees e
    UNION ALL
    SELECT e.EmployeeName, s.Amount AS Value, 'Actual' AS Metric
    FROM Sales s
    JOIN Employees e ON s.EmployeeID = e.EmployeeID
) AS SourceTable
PIVOT
(
    SUM(Value)
    FOR Metric IN ([Target], [Actual])
) AS PivotTable;

SELECT EmployeeName, [Target], [Actual]

FROM

(

SELECT e.EmployeeName, e.SalesTarget AS Value, 'Target' AS Metric

FROM Employees e

UNION ALL

SELECT e.EmployeeName, s.Amount AS Value, 'Actual' AS Metric

FROM Sales s

JOIN Employees e ON s.EmployeeID = e.EmployeeID

) AS SourceTable

PIVOT

(

SUM(Value)

FOR Metric IN ([Target], [Actual])

) AS PivotTable;

実行結果:

EmployeeName	Target	Actual
John Doe	50000	45000
Jane Smith	60000	65000

地域別販売数の集計

Regionsテーブルを結合して地域別の販売数を集計する例。PIVOTでRegionNameごとにSUM(Quantity)で販売数を集計し、各地域の販売実績を列に展開します。地域別の市場パフォーマンスを把握し、地域ごとの販売戦略を立てるのに役立ちます。

Regions (地域)

RegionID	RegionName
1	North
2	South
3	East
4	West

SELECT *
FROM (
    SELECT
        RegionName,
        Quantity
    FROM Sales
    JOIN Regions ON Sales.RegionID = Regions.RegionID
) AS SourceTable
PIVOT (
    SUM(Quantity)
    FOR RegionName IN ([North], [South], [East], [West])
) AS PivotTable;

SELECT *

FROM (

SELECT

RegionName,

Quantity

FROM Sales

JOIN Regions ON Sales.RegionID = Regions.RegionID

) AS SourceTable

PIVOT (

SUM(Quantity)

FOR RegionName IN ([North], [South], [East], [West])

) AS PivotTable;

実行結果:

RegionName	North	South	East	West
合計	1200	1500	1000	800

基本テーブル構造

Sales (販売データ)
- SaleDate: 販売日
- Amount: 販売額
- CategoryID: 商品カテゴリID
- EmployeeID: 従業員ID
- RegionID: 地域ID
- Quantity: 数量
Categories (商品カテゴリ)
- CategoryID: カテゴリID
- CategoryName: カテゴリ名
Employees (従業員)
- EmployeeID: 従業員ID
- EmployeeName: 従業員名
- SalesTarget: 販売目標
Regions (地域)
- RegionID: 地域ID
- RegionName: 地域名

おすすめ情報

ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数（ウインドウ関数）の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。