How to Create a Pivot Table in PostgreSQL (With Example)

Often in PostgreSQL you may want to create a pivot table to summarize the values in one column, grouped by two other columns.

You can use the following syntax to do so:

SELECT team,
   avg(points) FILTER (WHERE role = 'Guard') AS "Guard",
   avg(points) FILTER (WHERE role = 'Forward') AS "Forward"
FROM athletes
GROUP BY team;

This particular example calculates the average value in the points column, grouped by the values in the team and role columns.

The following example shows how to use this syntax in practice.

Example: How to Create a Pivot Table in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  role TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Forward', 40);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (0007, 'Hawks', 'Forward', 33);
INSERT INTO athletes VALUES (0008, 'Hawks', 'Guard', 12);

-- view all rows in table
SELECT * FROM athletes;

 id | team  |  role   | points 
----+-------+---------+--------
  1 | Mavs  | Guard   |     15
  2 | Mavs  | Guard   |     12
  3 | Mavs  | Forward |     23
  4 | Mavs  | Forward |     40
  5 | Hawks | Guard   |     29
  6 | Hawks | Forward |     14
  7 | Hawks | Forward |     33
  8 | Hawks | Guard   |     12

Suppose that we would like to create a pivot table that displays the average points scored for each player, grouped by team and role.

We can use the following syntax to do so:

SELECT team,
   avg(points) FILTER (WHERE role = 'Guard') AS "Guard",
   avg(points) FILTER (WHERE role = 'Forward') AS "Forward"
FROM athletes
GROUP BY team;

 team  |        Guard        |       Forward       
-------+---------------------+---------------------
 Mavs  | 13.5000000000000000 | 31.5000000000000000
 Hawks | 20.5000000000000000 | 23.5000000000000000

Notice that the resulting pivot table shows the average points scored, grouped by the values in the team and role columns.

For example, we can see:

• The average points scored by Guards on the Mavs is 13.5.
• The average points scored by Forwards on the Mavs is 31.5.
• The average points scored by Guards on the Hawks is 20.5.
• The average points scored by Forwards on the Hawks is 23.5.

Feel free to use similar syntax to create a pivot table for your own table.

Note: To display a different metric in the pivot table, simply change avg to a different aggregation metric such as sum, count, max, min, etc.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Find Maximum Value Across Multiple Columns
PostgreSQL: Find Minimum Value Across Multiple Columns