The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. The syntax is non-standard, and not all databases support the PIVOT syntax, but the functionality can be implemented, often using decision logic (CASE expressions, etc) and aggregate functions.
The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. The syntax is non-standard, and not all databases support the PIVOT syntax, but the functionality can be implemented, often using decision logic (CASE expressions, etc) and aggregate functions.
The PIVOT operation may also be referred to as cross-tabulation or crosstab.
Similar functionality also exists in data processing tools/packages such as pandas (via pivot method), tidyr (via pivot_wider function), and matlab (via unstack function).
Databases that support PIVOT/UNPIVOT syntax:
- Oracle 11g+
- SQL Server 2005+
While MySQL does not have a PIVOT function, this can be replicated using an aggregate function and either a CASE statement or IF()
PostgreSQL (true in 9.3 and below, at least) do not offer PIVOT, but do supply the crosstab function from the bundled tablefunc extension. This function may be used, albeit awkwardly, to achieve the same effect.
Reference:
- Using PIVOT and UNPIVOT in SQL Server
