Search code examples
sqlpostgresqlcrosstab

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

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
Solution

  • 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;