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 运算符。有两种方法：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 字段中（每列一个字段）。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 作业可以生成分组的、类似透视的目标索引。