PIVOT / UNPIVOT(行↔列)

Oracle

-- Turn sales by month into columns
SELECT * FROM (
  SELECT month, amount FROM sale
)
PIVOT (SUM(amount) FOR month IN ('Jan' AS jan, 'Feb' AS feb, 'Mar' AS mar));

-- UNPIVOT: turn columns back into rows
SELECT product, month, amount
FROM sale_wide
UNPIVOT (amount FOR month IN (jan AS 'Jan', feb AS 'Feb'));

PIVOT は行内の異なる値を列に変換し(クロス集計)、UNPIVOT は列を行に戻す。サマリー/レポート用のテーブルで使用される。Oracle は 11g 以降、組み込みの PIVOT/UNPIVOT 演算子を提供する。列リストは静的である必要がある(動的な列には PIVOT XML またはコードが必要)。

PostgreSQL

-- The tablefunc extension for crosstab
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
  'SELECT product, month, amount FROM sale ORDER BY 1,2'
) AS ct(product text, jan numeric, feb numeric, mar numeric);

-- Without an extension: conditional aggregation
SELECT product,
  SUM(amount) FILTER (WHERE month = 'Jan') AS jan,
  SUM(amount) FILTER (WHERE month = 'Feb') AS feb
FROM sale GROUP BY product;

PostgreSQL には組み込みの PIVOT 演算子がない。方法は 2 つ: tablefunc 拡張の crosstab() 関数、または移植性のある条件付き集計(SUM(...) FILTER (WHERE ...) または CASE を使用)。FILTER 句は PostgreSQL 固有のエレガントなピボット手法。

MySQL

-- There is no PIVOT; conditional aggregation with manual CASE
SELECT product,
  SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS mar
FROM sale
GROUP BY product;

MySQL/MariaDB には PIVOT/UNPIVOT 演算子がない。クロス集計は、GROUP BY と各ターゲット列に対する SUM(CASE WHEN ... THEN ... END) で手作業で構築する。列が動的な場合は、プリペアドステートメントでクエリを文字列として生成する。

SQL Server

SELECT * FROM (
  SELECT product, month, amount FROM sale
) src
PIVOT (
  SUM(amount) FOR month IN ([Jan], [Feb], [Mar])
) pvt;

-- Converting back with UNPIVOT is also supported

SQL Server は 2005 以降 PIVOT と UNPIVOT 演算子をネイティブにサポートする。PIVOT の列リストは角括弧で囲んだ静的な値として指定する。動的な列には動的 SQL でクエリを構築する。小さなサマリーには条件付き集計(CASE)が代替手段。

MongoDB

// PIVOT equivalent: conditional aggregation inside $group
db.sale.aggregate([
  { $group: {
      _id: "$product",
      jan: { $sum: { $cond: [ { $eq: ["$month", "Jan"] }, "$amount", 0 ] } },
      feb: { $sum: { $cond: [ { $eq: ["$month", "Feb"] }, "$amount", 0 ] } }
  } }
])

MongoDB には PIVOT 演算子がない。クロス集計は $group ステージで各ターゲット列に対して $sum + $cond(条件付き集計)で構築する。逆方向(UNPIVOT)では、$objectToArray と $unwind でフィールドを行に展開する。

Redis

# Not applicable: Redis has no pivot/cross tab.

Redis には行と列を入れ替える概念がない。サマリー/クロス集計はアプリケーション側で計算する。頻繁に要求されるサマリーは事前に計算して Hash フィールド(列ごとに 1 フィールド)に格納できる。RediSearch の FT.AGGREGATE はグループ化した集計を行えるが、本当のピボットではない。

Elasticsearch

# Closest equivalent: nested terms aggregations
GET sale/_search
{
  "size": 0,
  "aggs": { "product": { "terms": { "field": "product" },
    "aggs": { "month": { "terms": { "field": "month" },
      "aggs": { "total": { "sum": { "field": "amount" } } } } } }
}

Elasticsearch には PIVOT 演算子がない。クロス集計はネストした terms 集計(外側のキー / 内側のキー)で構築し、結果は列ではなくネストしたバケットとして返るため、クライアントで表に変換する。transform ジョブでグループ化されたピボット風のターゲットインデックスを生成できる。

DBAtlas で開く →