Pivot table on postgresql

Question

I'm trying to make a table pivot on a postgresql database but i can't succeed. Here is my original request

SELECT tag_country.label, COUNT(*)
FROM log_event_tracking LEFT JOIN user_account ON log_event_tracking.email = user_account.email LEFT JOIN tag_country ON user_account.country = tag_country.id
WHERE date_time > CURRENT_DATE - INTERVAL '7 days'
GROUP BY tag_country.label;

and here is the result

label	count
<null>	50
Spain	23
France	99

What i'm trying to do is to pivot this table to get this result

<null>	Spain	France
50	23	99

Does anyone have a solution ? Thanks for your answers.

EDIT :

Forgot to say that i might have more countries or less countries depending of the database i'm requesting and i need it works with the same query

Might be helpful https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — alex_noname, Nov 19 '21 at 14:47

score 2 · Answer 1 · edited Aug 10 '23 at 15:14

You can use the function crosstab from tablefunc extention, I'll put an exemple here to use it.

Here you'll need create the tablefunc extention if not exists

CREATE EXTENSION IF NOT EXISTS tablefunc;

CREATE TABLE exams (
     id bigserial NOT NULL,
     name text,
     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);

Now you can run your query converting it to a pivot table, the result you can see on the attached picture.

SELECT * FROM crosstab('select name, exam, score from exams order by 1,2')
         AS ct (name text,exam1 int, exam2 int, exam3 int, exam4 int);

result for pivot table

I hope this can help you man.

Hello, thank you for your answer, it's exactly what i'm trying to do. There is just one thing different : i'm trying to do it with a dynamic number of exam. Is there a way to indicate to crosstab that there is (name text, exams... int) or something like that — Benoit Bonavia, Nov 22 '21 at 08:48
Hello @BenoitBonavia , can you share your query or tables model to crarify better my understanding? — Targino Castelo Branco, Nov 28 '21 at 02:23

score 1 · Answer 2 · answered Nov 21 '21 at 02:03

You can get what you are after with SQL. However I suggest what you asking is not a very good idea, it involve way to much maintenance just to keep the query accurate without any other benefit. But first, how to do it. Well the tablefunc suggestion will work, another option is the Postgres filter clause.

select "<null>", "France", "Spain"
  from ( 
        select count(*) filter (where label is null) as "<null>"
             , count(*) filter (where label = 'France') as "France"
             , count(*) filter (where label = 'Spain')  as "Spain"
          from tag_country             
       ) sq;

The problem with both of them being you must know the exact pivot columns before running the query and update when modifications are made. Thus if you have more countries or less countries depending of the database I'm requesting and i need it works with the same query, cannot be realized. Your options are updating the query or writing an involved process to generate the SQL on the fly :( or pushing the pivoting off to the presentation layer in application. The query becomes a simple counting:

select label, count(*)
  from tag_country 
 group by label
 order by label nulls first;

SQL excels at slicing any dicing the data, but is very poor at formatting the results. Your presentation manager just needs to shift the vertical result set into a horizontal one. At which it should excel.

See demo;

Pivot table on postgresql

2 Answers2

Linked