pgsql - return row containing data from multiple rows

Question

I have a table formatted like this (obviously, simplified) :

session_id	timestamp	action	message
4de88be3-2316-4efa-8e17-58a2365534d9	2:04	4363d58b-c9fe-43a1-b636-c65822329aa3	initial
4de88be3-2316-4efa-8e17-58a2365534d9	2:05	d4294aaf-3fee-4154-a3de-b2f9c05a0cf1	queued
4de88be3-2316-4efa-8e17-58a2365534d9	2:10	dc40eaec-2aed-4b24-9b8e-ff1e25194036	connected
4de88be3-2316-4efa-8e17-58a2365534d9	2:32	dd93f0d4-7db9-4a68-876b-956db9300841	hangup

noting that multiple sessions could be happening at the same time of course, I'm trying to figure out how to write a query to return a row similar to this:

session_id	initial	connected	queued	hangup
4de88be3-2316-4efa-8e17-58a2365534d9	2:04	2:05	2:10	2:32

I'll be changing some of those values to instead show a duration, and some other work, but I just can't seem to figure out how to get this initial output of having a single row that uses multiple other rows to get its data (especially in an efficient way, I tried doing a for loop on the session ids, that was really slow)

I could do this in the backend in a coding language, having it dump the results to a temp table, but if it's possible to create a view like this, I would prefer that. I've been banging my head against the desk all morning trying to figure this out. Just need a push in the right direction before I lose my mind :)

Any help is greatly appreciated

Thanks again @Andomar My final query ended up being like this, which I can now use to build KPI reports. I guess I just needed to walk away from the problem for a few minutes and clear my head

select
    session_id,
    initial,
    queued - initial as time_to_queue,
    connected - queued as time_in_queue,
    hangup - connected as time_in_call,
    hangup - initial as total_time,
    hangup
from
(SELECT
    session_id,
    MIN(CASE WHEN action = '4363d58b-c9fe-43a1-b636-c65822329aa3' THEN timestamp END) AS initial,
    MIN(CASE WHEN action = 'd4294aaf-3fee-4154-a3de-b2f9c05a0cf1' THEN timestamp END) AS queued,
    MIN(CASE WHEN action = 'dc40eaec-2aed-4b24-9b8e-ff1e25194036' THEN timestamp END) AS connected,
    MIN(CASE WHEN action = 'dd93f0d4-7db9-4a68-876b-956db9300841' THEN timestamp END) AS hangup
FROM
    cdr
where date(((timestamp at TIME zone 'UTC') at TIME zone 'US/Eastern')::timestamptz) >= '2023-12-07'
GROUP BY
    session_id) x
where initial is not null --ignore outbound calls

Answer 1

If each event only happens once per session, you can group by on session, and take max() (or min()) of the timestamp for that message:

select  session_id,
,       max(case when message = 'initial' then timestamp end) as initial
,       max(case when message = 'connected' then timestamp end) as connected
,       max(case when message = 'queued' then timestamp end) as queued
,       max(case when message = 'hangup' then timestamp end) as hangup
from    YourTable
group by
        session_id