Can my DB?

-- Workaround: manual PIVOT using CASE expressions
SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM sales
GROUP BY product;

-- Workaround: crosstab() from the tablefunc extension
-- CREATE EXTENSION tablefunc;
SELECT *
FROM crosstab(
  'SELECT product, quarter, SUM(amount) FROM sales GROUP BY 1,2 ORDER BY 1,2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(product TEXT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC);

• No PIVOT keyword. The FILTER clause is the clean spelling: SUM(amount) FILTER (WHERE quarter = 'Q1'). CASE works everywhere.
• The tablefunc extension ships crosstab(), useful when you want a category-value map produced by a query.

-- Workaround: manual PIVOT using IF expressions
SELECT
  product,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2,
  SUM(IF(quarter = 'Q3', amount, 0)) AS q3,
  SUM(IF(quarter = 'Q4', amount, 0)) AS q4
FROM sales
GROUP BY product;

• No PIVOT keyword. CASE or IF inside aggregates with GROUP BY is the only option.
• Dynamic pivot needs application-side SQL generation, typically via GROUP_CONCAT and a prepared statement.

-- Workaround: manual PIVOT using CASE expressions
SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM sales
GROUP BY product;

• No PIVOT keyword. Same CASE / IF workaround and dynamic-SQL escape hatch as MySQL.

-- PIVOT: rows to columns
SELECT *
FROM sales
PIVOT (
  SUM(amount)
  FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4)
);

-- UNPIVOT: columns to rows
SELECT product, quarter, amount
FROM quarterly_sales
UNPIVOT (
  amount FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4')
);

• Added in 11g.
• PIVOT XML handles dynamic column lists, returning the rotated data as XML rather than a fixed column projection.

-- PIVOT: rows to columns
SELECT product, [Q1], [Q2], [Q3], [Q4]
FROM (
  SELECT product, quarter, amount FROM sales
) AS src
PIVOT (
  SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

-- UNPIVOT: columns to rows
SELECT product, quarter, amount
FROM quarterly_sales
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
) AS unpvt;

• Added in 2005. The source must be a subquery or CTE, not a bare table.
• Dynamic pivot requires EXEC or sp_executesql with a generated column list.

-- Workaround: manual PIVOT using CASE expressions
SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM sales
GROUP BY product;

• No PIVOT keyword. Use CASE inside aggregates with GROUP BY.

-- PIVOT: rows to columns
SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
  AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);

-- UNPIVOT: columns to rows
SELECT *
FROM count_by_color UNPIVOT (
  cnt FOR color IN (red, green, blue)
);

• PIVOT accepts COUNT, SUM, MIN, MAX, AVG. UNPIVOT accepts INCLUDE NULLS and IN-list aliases.
• PartiQL-style UNPIVOT also works on SUPER-typed objects.

-- Workaround: manual PIVOT using CASE expressions
SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM sales
GROUP BY product;

• No PIVOT keyword. CASE inside aggregates is the common approach; XMLTABLE handles more elaborate reshaping.
• Dynamic pivot requires application-side SQL generation.

-- PIVOT: rows to columns
SELECT *
FROM sales
PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

-- Dynamic PIVOT with ANY (Snowflake-specific)
SELECT *
FROM sales
PIVOT (SUM(amount) FOR quarter IN (ANY ORDER BY quarter));

-- UNPIVOT: columns to rows
SELECT product, quarter, amount
FROM quarterly_sales
UNPIVOT (amount FOR quarter IN (q1, q2, q3, q4));

• Dynamic pivot is built in: IN (ANY [ORDER BY ...]) rotates every distinct value with no generated SQL.
• UNPIVOT accepts INCLUDE NULLS / EXCLUDE NULLS.

-- PIVOT: rows to columns
SELECT *
FROM sales
PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

-- UNPIVOT: columns to rows
SELECT product, quarter, amount
FROM quarterly_sales
UNPIVOT (amount FOR quarter IN (q1, q2, q3, q4));

• Added in 2022. IN-list aliases (IN ('Q1' AS q1, ...)) let you rename the pivoted columns.
• UNPIVOT accepts INCLUDE NULLS / EXCLUDE NULLS.