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

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

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

CodePudding user response：

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.

CodePudding user response：

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;