When to use CASE pivot vs PIVOT keyword

Question

Accepted Answer

PostgreSQL (and most ANSI-compliant databases) do not have a PIVOT keyword. SQL Server and Oracle do, but they are vendor-specific extensions. Conditional aggregation - SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) - is the portable solution that works everywhere. The downside is that you must hard-code each column name; if the set of values changes, you update the query. For dynamic column sets, some databases offer crosstab functions or application-level pivoting.