SQL Serverの列から行への変換：PIVOTテーブルとクロス集計を超えた高度なテクニック

SQL Server: 列から行への変換 - プログラミング解説

SQL Server において、列データを横向きに並べた形式（列形式）から、縦向きに並べ替えた形式（行形式）に変換することは、分析や可視化において有用な場面が多くあります。この操作は、PIVOT テーブルやクロス集計などの機能を用いて実現できます。

本記事では、SQL Serverにおける列から行への変換について、わかりやすくプログラミング解説を行います。

PIVOT テーブルは、列形式のデータを自動的に行形式に変換するための便利な機能です。

例：顧客ごとの売上分析

売上データのテーブル sales が存在する場合、顧客ごとの売上を月別に集計して行形式で表示したいことがあります。

-- テーブル構造 (例)
CREATE TABLE sales (
  customer_id INT,
  order_date DATE,
  sales_amount DECIMAL(10,2)
);

PIVOT テーブルを用いると、以下のように簡単に集計結果を取得できます。

SELECT
  customer_id,
  [2023-01] AS '1月',
  [2023-02] AS '2月',
  [2023-03] AS '3月',
  ...
FROM sales
PIVOT
(
  SUM(sales_amount)
  FOR order_date IN ([2023-01], [2023-02], [2023-03], ...)
) AS data;

クロス集計は、より柔軟な集計と行形式への変換を可能にする機能です。

例：商品カテゴリごとの売上と利益の分析

-- テーブル構造 (例)
CREATE TABLE sales (
  product_category VARCHAR(20),
  order_date DATE,
  sales_amount DECIMAL(10,2),
  profit_amount DECIMAL(10,2)
);

SELECT
  product_category,
  [2023-01] AS '1月_売上',
  [2023-01] AS '1月_利益',
  [2023-02] AS '2月_売上',
  [2023-02] AS '2月_利益',
  ...
FROM sales
CROSS APPLY
(
  SELECT
    order_date,
    SUM(sales_amount) AS sales_amount,
    SUM(profit_amount) AS profit_amount
  FROM sales
  WHERE product_category = current_product_category
  GROUP BY order_date
) AS data
ORDER BY product_category;

その他のテクニック

上記の例以外にも、CASE 式やウィンドウ関数などを組み合わせることで、より複雑な列から行への変換を実現できます。

SQL Server における列から行への変換は、PIVOT テーブルやクロス集計などの機能を活用することで、効率的に行うことができます。状況に応じて適切な方法を選択し、データ分析や可視化に役立ててください。

SQL Server: 列から行への変換 - サンプルコード

PIVOT テーブル

例：顧客ごとの売上分析（前述と同じデータを使用）

-- テーブル構造 (例)
CREATE TABLE sales (
  customer_id INT,
  order_date DATE,
  sales_amount DECIMAL(10,2)
);

-- データ挿入 (例)
INSERT INTO sales VALUES
  (1, '2023-01-01', 100.00),
  (1, '2023-02-05', 150.00),
  (1, '2023-03-12', 220.00),
  (2, '2023-01-15', 80.00),
  (2, '2023-02-20', 125.00),
  (2, '2023-03-27', 180.00);

以下のクエリを実行すると、顧客ごとの売上を月別に集計した行形式の結果が得られます。

SELECT
  customer_id,
  [2023-01] AS '1月',
  [2023-02] AS '2月',
  [2023-03] AS '3月'
FROM sales
PIVOT
(
  SUM(sales_amount)
  FOR order_date IN ([2023-01], [2023-02], [2023-03])
) AS data;

結果

customer_id | 1月 | 2月 | 3月
-----------+-----+-----+-----
1          | 100.00 | 150.00 | 220.00
2          | 80.00 | 125.00 | 180.00

クロス集計

-- テーブル構造 (例) -- 前述と同じ
-- データ挿入 (例) -- 前述と同じ

SELECT
  product_category,
  [2023-01_売上] AS '1月_売上',
  [2023-01_利益] AS '1月_利益',
  [2023-02_売上] AS '2月_売上',
  [2023-02_利益] AS '2月_利益',
  ...
FROM sales
CROSS APPLY
(
  SELECT
    order_date,
    SUM(sales_amount) AS sales_amount,
    SUM(profit_amount) AS profit_amount
  FROM sales
  WHERE product_category = current_product_category
  GROUP BY order_date
) AS data
ORDER BY product_category;

product_category | 1月_売上 | 1月_利益 | 2月_売上 | 2月_利益 | ...
-----------------+-----------+------------+-----------+------------+
Clothing         | 100.00 | 50.00 | 150.00 | 75.00 | ...
Electronics      | 80.00 | 40.00 | 125.00 | 62.50 | ...

例：CASE 式を用いた売上区分ごとの売上分析

-- テーブル構造 (例)
CREATE TABLE sales (
  order_id INT,
  sales_date DATE,
  sales_amount DECIMAL(10,2),
  payment_method VARCHAR(20)
);

-- データ挿入 (例)
INSERT INTO sales VALUES
  (1, '2023-01-01', 100.00, '

SQL Server: 列から行への変換 - その他の方法

本記事では、SQL Serverにおける列から行への変換について、PIVOT テーブルやクロス集計以外にも利用可能な方法をいくつかご紹介します。

サブクエリを用いる方法も、列から行への変換を実現できます。

-- テーブル構造 (例) -- 前述と同じ
-- データ挿入 (例) -- 前述と同じ

SELECT
  customer_id,
  (
    SELECT SUM(sales_amount) FROM sales AS s2
    WHERE s2.customer_id = s1.customer_id AND MONTH(s2.order_date) = 1
  ) AS '1月',
  (
    SELECT SUM(sales_amount) FROM sales AS s2
    WHERE s2.customer_id = s1.customer_id AND MONTH(s2.order_date) = 2
  ) AS '2月',
  ...
FROM sales AS s1
GROUP BY s1.customer_id;

-- テーブル構造 (例) -- 前述と同じ
-- データ挿入 (例) -- 前述と同じ

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

-- ピボット列を動的に生成
SET @pivot_column = N'';
SELECT @pivot_column = @pivot_column + CONCAT('[', MONTH(order_date), ']', ' AS ', QUOTENAME(MONTH(order_date)), ',')
FROM sales
GROUP BY MONTH(order_date)
ORDER BY MONTH(order_date);

-- 動的 SQL を生成
SET @sql = N'
SELECT
  product_category,
  ' + @pivot_column + '
FROM sales
PIVOT
(
  SUM(sales_amount)
  FOR order_date IN (' + @pivot_column.SUBSTRING(1, LEN(@pivot_column) - 1) + ')
) AS data
ORDER BY product_category;
';

-- 動的 SQL を実行
EXEC sp_executesql @sql;

ウィンドウ関数を用いる方法も、集計と行形式への変換を組み合わせることができます。

例：売上ランキング上位5件の分析

-- テーブル構造 (例)
CREATE TABLE sales (
  product_id INT,
  sales_date DATE,
  sales_amount DECIMAL(10,2)
);

-- データ挿入 (例)
INSERT INTO sales VALUES
  (1, '2023-01-01', 150.00),
  (2, '2023-02-05', 120.00),
  (3, '2023-03-12', 100.00),
  (4, '2023-01-15', 85.00),
  (5, '2023-02-20', 90.00),
  (6, '2023-03-27', 70.00);

以下のクエリを実行すると、売上ランキング上位5件の売上情報が得られます。

SELECT
  product_id,
  sales_date,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales
ORDER BY rank
WHERE rank <= 5;

その他

上記以外にも、さまざまな方法で列から行への変換を実現できます。状況に応じて適切な方法を選択することが重要です。