CODEWITHSUNDEEP
sqlpostgresql
Raphael Pr
Raphael Pr

Reputation: 954

Last Value in a Group with one column per type

I have a table in the following format :

owner_id question response created_at
1 dog_name Rex 2021-05-31
1 cat_name Old Kitty 2021-05-01
1 cat_name New Kitty 2021-05-30
2 cat_name Bella 2021-05-28
3 bird_name Birdy 2021-05-28

And I would like to be able have the list of owner with each pet's name :

owner_id dog_name cat_name bird_name
1 Rex New Kitty NULL
2 NULL Bella NULL
3 NULL NULL Birdy

I have manage to list all the data that I need with the following query :

SELECT DISTINCT ON (owner_id, question) owner_id, question, response
FROM pets
ORDER BY owner_id, question, created_at DESC

It output what I need. However, I need to transpose the data and having dog_name / cat_name / etc. (finite list of pet type) in column.

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use aggregation and arrays for this:

select owner_id,
       (array_agg(response order by created_at desc) filter (where question = 'dog_name'))[1],
       (array_agg(response order by created_at desc) filter (where question = 'cat_name'))[1],
       (array_agg(response order by created_at desc) filter (where question = 'bird_name'))[1]
from t
group by owner_id;

Here is a db<>fiddle.

Another method is conditional aggregation after retrieving the most recent row for each owner/question combination:

select owner_id,
       max(response) filter (where question = 'dog_name'),
       max(response) filter (where question = 'cat_name'),
       max(response) filter (where question = 'bird_name')
from (select distinct on (owner_id, question) t.*
      from t
      order by owner_id, question, created_at desc
     ) t
group by owner_id;

Upvotes: 1

Isolated
Isolated

Reputation: 6454

You could use CTE's. Easy to read, but this could get pretty long if having multiple pet names. Not sure how you want to handle occasions where someone has more than one pet of same species, so that would impact this answer, too.

    with dogs as
     (select distinct owner_id, response
     from pets
     where question = 'dog_name'
     ), 
    cats as 
     (select distinct owner_id, response
     from pets
     where question = 'cat_name'
     ), 
    birds as
     (select distinct owner_id, response
     from pets
     where question = 'bird_name'
     )
    select distinct p.owner_id, 
     d.response as dog_name, 
     c.response as cat_name, 
     b.response as bird_name
    from pets p
    left join dogs d
     on d.owner_id = p.owner_id
    left join cats c
     on c.owner_id = p.owner_id
    left join birds b
     on b.owner_id = p.owner_id

Based on your comment (using the most recent response), I modified the answser as:

    with dogs as
     (select distinct owner_id, response, 
     rank() over (partition by owner_id order by created_at desc) as p_rank
     from pets
     where question = 'dog_name'
     ), 
    cats as 
     (select distinct owner_id, response, 
     rank() over (partition by owner_id order by created_at desc) as p_rank
     from pets
     where question = 'cat_name'
     ), 
    birds as
     (select distinct owner_id, response, 
     rank() over (partition by owner_id order by created_at desc) as p_rank
     from pets
     where question = 'bird_name'
     )
    select distinct p.owner_id, 
     d.response as dog_name, 
     c.response as cat_name, 
     b.response as bird_name
    from pets p
    left join dogs d
     on d.owner_id = p.owner_id
    left join cats c
     on c.owner_id = p.owner_id
    left join birds b
     on b.owner_id = p.owner_id
    where (d.p_rank is null or d.p_rank = 1)
    and (c.p_rank is null or c.p_rank = 1)
    and (b.p_rank is null or b.p_rank = 1)

Upvotes: 1

Related Questions