「Reshape a Table to Convert Rows to Columns」をSQL、MySQL、ピボットテーブルで実現

2024-04-12

SQL、MySQL、ピボットテーブルを使ってテーブルの形状を変更し、行を列に変換する方法

このチュートリアルでは、SQL、MySQL、ピボットテーブルを使ってテーブルの形状を変更し、行を列に変換する方法について説明します。

前提条件

SQLとMySQLの基本的な知識
ピボットテーブルの概念

使用するツール

MySQL 8.0
MySQL Workbench

手順

サンプルデータの準備

以下のサンプルデータ products テーブルを用意します。

CREATE TABLE products (
  id INT,
  category VARCHAR(255),
  name VARCHAR(255),
  price DECIMAL(10,2)
);

INSERT INTO products (id, category, name, price) VALUES
  (1, 'Electronics', 'Smartphone', 500.00),
  (2, 'Electronics', 'Tablet', 300.00),
  (3, 'Clothing', 'T-Shirt', 20.00),
  (4, 'Clothing', 'Jeans', 50.00),
  (5, 'Food', 'Apple', 2.50),
  (6, 'Food', 'Orange', 3.00);

PIVOT テーブルを使用した行から列への変換

PIVOT 関数を使用して、category 列を軸にして行を列に変換します。

SELECT category,
  MAX(CASE WHEN name = 'Smartphone' THEN price END) AS Smartphone,
  MAX(CASE WHEN name = 'Tablet' THEN price END) AS Tablet,
  MAX(CASE WHEN name = 'T-Shirt' THEN price END) AS TShirt,
  MAX(CASE WHEN name = 'Jeans' THEN price END) AS Jeans,
  MAX(CASE WHEN name = 'Apple' THEN price END) AS Apple,
  MAX(CASE WHEN name = 'Orange' THEN price END) AS Orange
FROM products
GROUP BY category;

結果

category | Smartphone | Tablet | TShirt | Jeans | Apple | Orange
------- | -------- | -------- | -------- | -------- | -------- | --------
Electronics | 500.00 | 300.00 | NULL | NULL | NULL | NULL
Clothing | NULL | NULL | 20.00 | 50.00 | NULL | NULL
Food | NULL | NULL | NULL | NULL | 2.50 | 3.00

解説

PIVOT 関数は、最初の引数で軸となる列を指定します。この例では category 列を軸にしています。
2番目以降の引数は、各列の値を計算するための式を指定します。この例では、CASE 式を使用して、各カテゴリにおける各製品の価格を取得しています。
GROUP BY 句は、軸となる列に基づいてグループ化を行います。

CASE 式と UNION ALL を使用

SELECT 'Electronics' AS category, 'Smartphone' AS name, 500.00 AS price
UNION ALL
SELECT 'Electronics', 'Tablet', 300.00
UNION ALL
SELECT 'Clothing', 'T-Shirt', 20.00
UNION ALL
SELECT 'Clothing', 'Jeans', 50.00
UNION ALL
SELECT 'Food', 'Apple', 2.50
UNION ALL
SELECT 'Food', 'Orange', 3.00;

動的 SQL を使用

SET @sql = 'SELECT category, ';

SELECT CONCAT(@sql, "'", name, "' AS ", name, ", ")
FROM products
GROUP BY category;

SET @sql = CONCAT(@sql, 'FROM products GROUP BY category;');

PREPARE stmt FROM @sql;
EXECUTE stmt;

[MySQL 8.0 リファレンスマニュアル :: 13.7.

CREATE TABLE products (
  id INT,
  category VARCHAR(255),
  name VARCHAR(255),
  price DECIMAL(10,2)
);

INSERT INTO products (id, category, name, price) VALUES
  (1, 'Electronics', 'Smartphone', 500.00),
  (2, 'Electronics', 'Tablet', 300.00),
  (3, 'Clothing', 'T-Shirt', 20.00),
  (4, 'Clothing', 'Jeans', 50.00),
  (5, 'Food', 'Apple', 2.50),
  (6, 'Food', 'Orange', 3.00);

SELECT category,
  MAX(CASE WHEN name = 'Smartphone' THEN price END) AS Smartphone,
  MAX(CASE WHEN name = 'Tablet' THEN price END) AS Tablet,
  MAX(CASE WHEN name = 'T-Shirt' THEN price END) AS TShirt,
  MAX(CASE WHEN name = 'Jeans' THEN price END) AS Jeans,
  MAX(CASE WHEN name = 'Apple' THEN price END) AS Apple,
  MAX(CASE WHEN name = 'Orange' THEN price END) AS Orange
FROM products
GROUP BY category;

その他の方法

SELECT 'Electronics' AS category, 'Smartphone' AS name, 500.00 AS price
UNION ALL
SELECT 'Electronics', 'Tablet', 300.00
UNION ALL
SELECT 'Clothing', 'T-Shirt', 20.00
UNION ALL
SELECT 'Clothing', 'Jeans', 50.00
UNION ALL
SELECT 'Food', 'Apple', 2.50
UNION ALL
SELECT 'Food', 'Orange', 3.00;

SET @sql = 'SELECT category, ';

SELECT CONCAT(@sql, "'", name, "' AS ", name, ", ")
FROM products
GROUP BY category;

SET @sql = CONCAT(@sql, 'FROM products GROUP BY category;');

PREPARE stmt FROM @sql;
EXECUTE stmt;

サンプルコードの解説

このサンプルコードは、products というテーブルを作成し、いくつかのサンプルデータを挿入します。このテーブルには、id、category、name、price という4つの列があります。

このクエリは、PIVOT 関数を使用して、category 列を軸にして行を列に変換します。MAX 関数を使用して、各カテゴリにおける各製品の価格を取得します。

このクエリは、CASE 式と UNION ALL を使用して、行を列に変換します。

まとめ

SELECT category,
  (SELECT price FROM products WHERE category = 'Electronics' AND name = 'Smartphone') AS Smartphone,
  (SELECT price FROM products WHERE category = 'Electronics' AND name = 'Tablet') AS Tablet,
  (SELECT price FROM products WHERE category = 'Clothing' AND name = 'T-Shirt') AS TShirt,
  (SELECT price FROM products WHERE category = 'Clothing' AND name = 'Jeans') AS Jeans,
  (SELECT price FROM products WHERE category = 'Food' AND name = 'Apple') AS Apple,
  (SELECT price FROM products WHERE category = 'Food' AND name = 'Orange') AS Orange
FROM products
GROUP BY category;

JOIN を使用

SELECT p1.category,
  p1.price AS Smartphone,
  p2.price AS Tablet,
  p3.price AS TShirt,
  p4.price AS Jeans,
  p5.price AS Apple,
  p6.price AS Orange
FROM products p1
LEFT JOIN products p2 ON p1.category = p2.category AND p2.name = 'Tablet'
LEFT JOIN products p3 ON p1.category = p3.category AND p3.name = 'T-Shirt'
LEFT JOIN products p4 ON p1.category = p4.category AND p4.name = 'Jeans'
LEFT JOIN products p5 ON p1.category = p5.category AND p5.name = 'Apple'
LEFT JOIN products p6 ON p1.category = p6.category AND p6.name = 'Orange'
WHERE p1.name = 'Smartphone'
GROUP BY p1.category;

ウィンドウ関数を使用

SELECT category,
  MAX(CASE WHEN name = 'Smartphone' THEN price END) OVER (PARTITION BY category) AS Smartphone,
  MAX(CASE WHEN name = 'Tablet' THEN price END) OVER (PARTITION BY category) AS Tablet,
  MAX(CASE WHEN name = 'T-Shirt' THEN price END) OVER (PARTITION BY category) AS TShirt,
  MAX(CASE WHEN name = 'Jeans' THEN price END) OVER (PARTITION BY category) AS Jeans,
  MAX(CASE WHEN name = 'Apple' THEN price END) OVER (PARTITION BY category) AS Apple,
  MAX(CASE WHEN name = 'Orange' THEN price END) OVER (PARTITION BY category) AS Orange
FROM products
GROUP BY category;