grouping rows while being able to show columns from each row in the group

I’m sure the title is not clear enough, couldn’t find a better way to explain what I need.

I’m using PostGreSQL 11.4

i’ll minify the problem i’m having to show the actual issue.

I have a query that returns the following:

id  |  time | direction | name
------------------------------
1       0        1        foo1
1       0        2        foo2
1       1        1        foo3
1       1        2        foo4
2       2        1        foo5
2       2        2        foo6
3       3        2        foo7

what I would like to do is to group by id, time and to add two new columns: name_direction1 and name_direction2

so the result table would be:

id | time | name_direction1 | name_direction2

1     0        foo1               foo2
1     1        foo3               foo4
2     2        foo5               foo6
3     3                           foo7

as far as I know with group by I can’t really reach each one of the rows. in my case I need to combine two rows when both directions are presented.

any ideas how to achieve this?

thank you!

Answer

With conditional aggregation:

select 
  id, time,
  max(case direction when 1 then name end) name_direction_1,
  max(case direction when 2 then name end) name_direction_2
from tablename
group by id, time

See the demo.
Results:

| id  | time | name_direction_1 | name_direction_2 |
| --- | ---- | ---------------- | ---------------- |
| 1   | 0    | foo1             | foo2             |
| 1   | 1    | foo3             | foo4             |
| 2   | 2    | foo5             | foo6             |
| 3   | 3    |                  | foo7             |