PIVOT / UNPIVOT (rows↔columns)

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 turns distinct values in rows into columns (a cross tab), and UNPIVOT turns columns back into rows; used in summary/report tables. Oracle offers built-in PIVOT/UNPIVOT operators since 11g; the column list must be static (dynamic columns require PIVOT XML or code).

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 has no built-in PIVOT operator. There are two ways: the crosstab() function of the tablefunc extension, or portable conditional aggregation (SUM(...) FILTER (WHERE ...) or with CASE). The FILTER clause is an elegant PostgreSQL-specific pivot technique.

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 have no PIVOT/UNPIVOT operator; a cross tab is built by hand with GROUP BY plus a SUM(CASE WHEN ... THEN ... END) for each target column. If the columns are dynamic, the query is generated as a string with a prepared statement.

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 supports the PIVOT and UNPIVOT operators natively since 2005. The PIVOT column list is given as static bracketed values; for dynamic columns the query is built with dynamic SQL. For small summaries, conditional aggregation (CASE) is an alternative.

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 has no PIVOT operator; a cross tab is built in the $group stage with $sum + $cond (conditional aggregation) for each target column. For the reverse direction (UNPIVOT), fields are expanded into rows with $objectToArray and $unwind.

Redis

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

Redis has no concept of rotating rows and columns. Summaries/cross tabs are computed on the application side; frequently requested summaries can be precomputed and stored in Hash fields (one field per column). RediSearch FT.AGGREGATE can do grouped aggregation, but it is not a real pivot.

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 has no PIVOT operator; a cross summary is built with nested terms aggregations (outer key / inner key), the result comes back as nested buckets rather than columns, and is turned into a table on the client. A transform job can produce a grouped, pivot-like target index.