Pivot table is a useful way to analyze large quantity of data by organizing it into a more manageable format. Here’s how to create pivot table in PostgreSQL. In other words, we will create crosstab in PostgreSQL.
How to Create Pivot Table in PostgreSQL
There are at least a couple of ways to create pivot table in PostgreSQL. One is where we pivot rows to columns in PostgreSQL using CASE statement, and another is a simple example of PostgreSQL crosstab function.
Let’s say you have the following table
CREATE TABLE Meeting
(
ID INT,
Meeting_id INT,
field_key VARCHAR(100),
field_value VARCHAR(100)
);
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (1, 1,'first_name' , 'Alec');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (2, 1,'last_name' , 'Jones');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (3, 1,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (4,2,'first_name' , 'John');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (5,2,'last_name' , 'Doe');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (6,2,'occupation' , 'engineer');
+------+------------+------------+-------------+
| ID | Meeting_id | field_key | field_value |
+------+------------+------------+-------------+
| 1 | 1 | first_name | Alec |
| 2 | 1 | last_name | Jones |
| 3 | 1 | occupation | engineer |
| 4 | 2 | first_name | John |
| 5 | 2 | last_name | Doe |
| 6 | 2 | occupation | engineer |
+------+------------+------------+-------------+
Let’s say you want to create pivot table in PostgreSQL, such that a new column is created for each unique value in field_key column, that is (first_name, last_name, occupation) as shown below
+------------+-------------+-------------+-------------+ | Meeting_id | first_name | last_name | occupation | +------------+-------------+-------------+-------------+ | 1 | Alec | Jones | engineer | | 2 | John | Doe | engineer | +------------+-------------+-------------+-------------+
Create Pivot Table in PostgreSQL using CASE statement
You can easily transpose rows into columns in above table using CASE statement,
postgresql> select meeting_Id,
max(case when (field_key='first_name') then field_value else NULL end) as first_name,
max(case when (field_key='last_name') then field_value else NULL end) as last_name,
max(case when (field_key='occupation') then field_value else NULL end) as occupation
from meeting
group by meeting_Id
order by meeting_Id;
+------------+------------+-----------+------------+
| meeting_Id | first_name | last_name | occupation |
+------------+------------+-----------+------------+
| 1 | Alec | Jones | engineer |
| 2 | John | Doe | engineer |
+------------+------------+-----------+------------+
In the above statement, each row’s field_key value is checked and accordingly the columns are populated. For example, if field_key value is ‘first_name’ then first_name column is populated, and so on.
Once you create pivot table in PostgreSQL, you can use a reporting tool to plot it in a table. Here’s an example of pivot table created using Ubiq. Did you know that Ubiq allows you to create pivot tables without writing any SQL?
Bonus read: How to Calculate Running Total in Redshift
Create Pivot Table in PostgreSQL using Crosstab function
PostgreSQL also provides a built-in Crosstab function that allows you to easily create pivot table in PostgreSQL. However, you need to install the table_func extension to enable Crosstab function.
postgresql> CREATE EXTENSION IF NOT EXISTS tablefunc;
Let’s say you have the following table.
CREATE TABLE exams (
id int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (id)
);
insert into exams (name,exam,score) values ('Bob',1,70);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,71);
insert into exams (name,exam,score) values ('Bob',4,70);
insert into exams (name,exam,score) values ('Sue',1,89);
insert into exams (name,exam,score) values ('Sue',2,87);
insert into exams (name,exam,score) values ('Sue',3,88);
insert into exams (name,exam,score) values ('Sue',4,89);
mysql> select * from exams;
+------+------+------+-------+
| id | name | exam | score |
+------+------+------+-------+
| 1 | Bob | 1 | 70 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 71 |
| 4 | Bob | 4 | 70 |
| 5 | Sue | 1 | 89 |
| 6 | Sue | 2 | 87 |
| 7 | Sue | 3 | 88 |
| 8 | Sue | 4 | 89 |
+------+------+------+-------+
Let’s say you want to pivot the above table by Exam column, such that for each student you get 1 row, with all exam scores as separate columns, as shown below.
name | exam1 | exam2 | exam3 | exam4 ------+-------+-------+-------+------- Bob | 70 | 77 | 71 | 70 Sue | 89 | 87 | 88 | 89
Since we have enabled Crosstab function for our database, you can use the following query to create a crosstab in PostgreSQL.
postgres=# SELECT * FROM crosstab('select name, exam, score from exams order by 1,2')
AS ct (name varchar(15),exam1 int, exam2 int, exam3 int, exam4 int);
name | exam1 | exam2 | exam3 | exam4
------+-------+-------+-------+-------
Bob | 70 | 77 | 71 | 70
Sue | 89 | 87 | 88 | 89
Crosstab works with a SELECT query as its input parameter which must follow 3 requirements
- It must return 3 columns
- The first column is the row identifier for your final pivot table e.g name
- The 2nd column is the category column to be pivoted e.g exam
- The 3rd column is the value column that you want to pivot e.g score
The Crosstab will take the result of your SELECT query, and build pivot table out of it, based on the columns you mention for your pivot table. In the above query, the pivot table is stored in a temporary table ct(name varchar(15),exam1 int, exam2 int, exam3 int, exam4 int). You have to define the column names and data types of your final pivot table.
Bonus read: How to Calculate Moving Average in Redshift
In our opinion, if you want to create pivot table in PostgreSQL, we found Crosstab method to be more difficult than using CASE statement, mainly because Crosstab throws errors if you don’t define column data types of final pivot table correctly.
Nevertheless, now you know two ways to create pivot table in PostgreSQL. You can customize them as per your requirement.
Did you know you can create pivot tables in Ubiq using just drag & drop?
By the way, if you want to create pivot tables, charts & dashboards from PostgreSQL database, you can try Ubiq. We offer a 14-day free trial.
