Question 313979 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I pivot a table so that the rows become columns?

I have a table like this named people:

first_name id total type
Joe 1234 13 onsite
Joe 1234 9 remote
Jane 4321 99 remote
Jane 4321 1 onsite

I want to query it and have the results returned like this:

first_name id onsite remote
Joe 1234 13 9
Jane 4321 1 99

I believe I need to use Pivot() but this is advanced for me

Thank you

I tried grouping by first_name but that doesn’t move the rows to columns

Tags:

2

Answers


  1. 0

    Here’s the conditional aggregation method, which is easy to comprehend and IMO recommended if you have just a couple different "types".

    select 
 first_name, 
 id, 
 max(case when type = 'onsite' then total end) as onsite, 
 max(case when type = 'remote' then total end) as remote
from people
group by first_name, id
    Login or Signup to reply.
  2. 0

    Use aggregate filter clause. Demo at db<>fiddle:

    select first_name,
       id, 
       sum(total)filter(where type='onsite') as onsite,
       sum(total)filter(where type='remote') as remote
from test
group by first_name,
         id;
    first_name id onsite remote
    Joe 1234 13 9
    Jane 4321 1 99

    Or look into crosstab().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search