Pivot functions in PostgreSQL

SQL Server provides PIVOT and UNPIVOT functions to create pivot tables. Unfortunately, PostgreSQL does implement them. However, it provides crosstab function from tablefunc extensions which is equivalent to PIVOT.

Let’s start by explaining what pivot tables actually are.

Pivot tables

Pivot tables allow to switch rows with columns and vice-versa to present results in a more visualized way. It’s best to explain them by example.

Having a table with student’s grades:

Moving Subject from rows to columns (pivot) will result in:

Moving columns (Subject and Grade) to rows (unpivot) will result in:

Pivoting PostgreSQL

The crosstab function from tablefunc extension allows performing a pivot. It receives SQL query following requirements as:

• it must return 3 columns
• the first column needs to identify each row of the result table (student in our example)
• the second column represents categories of pivot table - values will expand to columns of the table
• the third column represents values assigned to each cell

Query backing the pivot example from above looks as follows:

SELECT * 
FROM crosstab('SELECT student, subject, grade FROM grades order by 1,2') 
  AS final_result(Student TEXT, Geography FLOAT, History FLOAT, Math Float);

The unpivot one uses JOIN LATERAL though (we have to cast grade column so it is of the same type as subject):

SELECT student, name, value
FROM grades
JOIN LATERAL (VALUES('subject', grades.subject), ('grade', cast(grade AS text))) s(name, value) ON true

Full example can be found on dbfidle.