How do I pivot values from rows into column names in Postgres?

Question

I have a query in Postgres. It's working well and returns exactly the rows I'm looking for.

Here's the query:

select distinct on (status) id, status, timestamp
from my_table
where id = 1
order by status, timestamp
limit 500

This is what it returns:

id	status	timestamp
1	started	2022-01-15 05:12:36
1	paused	2022-04-14 09:12:50
1	killed	2022-04-27 13:12:48

How can I pivot this table to return this exactly:

id	started	paused	killed
1	2022-01-15 05:12:36	2022-04-14 09:12:50	2022-04-27 13:12:48

Answer 1

You could use the extension tablefunc, but it looks like a simple aggregate using a filter already works:

SELECT  id
    ,   MIN(timestamp) FILTER (WHERE status = 'started') AS started
    ,   MIN(timestamp) FILTER (WHERE status = 'paused') AS paused
    ,   MIN(timestamp) FILTER (WHERE status = 'killed') AS killed
FROM    my_table
WHERE   id = 1
GROUP BY id;