Create pivot table in Postgres 9.6

I am having trouble creating the desired output using SQL. I have an events table, that holds a record for every event taken by each candidate, as so:

| id | asmnt   | timestamp           | score |
|----|---------|---------------------|-------|
| 1  | pushups | 2020-06-21 12:31:12 | 34    |
| 1  | situps  | 2020-06-21 13:31:12 | 65    |
| 1  | run     | 2020-06-22 12:31:12 | 901   |
| 1  | pullups | 2020-06-21 14:31:12 | 15    |
| 2  | pushups | 2020-06-21 12:31:12 | 13    |
| 2  | situps  | 2020-06-21 13:31:12 | 21    |
| 2  | run     | 2020-06-22 12:31:12 | 1401  |
| 2  | pullups | 2020-06-21 14:31:12 | 3     |
| 2  | pushups | 2020-06-23 12:31:12 | 31    |
| 2  | situps  | 2020-06-23 13:31:12 | 45    |
| 2  | run     | 2020-06-24 12:31:12 | 1101  |
| 2  | pullups | 2020-06-23 14:31:12 | 13    |

Can I create a pivot table from this? I tried to use the crosstab extension, but due to the fact that each group (by id) will not be the same size, I am getting an error (not surprising). It is important to retain order (asmnt), as well as order by timestamp.

This is the output that I would like:

| id | pushups | situps | run | pullups |
|----|---------|--------|-----|---------|
| 1  | 34      | 65     | 901 |   15    |
| 2  | 31      | 45     | 1101|   13    |

Here is the SQL that I have tried (asmnt: APFPS, APFSU, APF2M or APFPL):

select *
from crosstab('select brandi_id, asmnt_code, score
from event
where left(asmnt_code,3) = ''APF''
order by brandi_id, asmnt_code, event_timestamp') 
    as events(brandi_id INTEGER,APF2M TEXT,APFPL TEXT,APFPS TEXT,APFSU TEXT,score INTEGER);

Answer

I understand that you want the score of the latest asmnt per id, in a pivoted resultset.

If so, you can use distinct on to get the latest record per group, and then conditional aggregation to pivot:

select
    id,
    max(score) filter(where asmnt = 'pushups') pushups,
    max(score) filter(where asmnt = 'situps') situps,
    max(score) filter(where asmnt = 'run') run,
    max(score) filter(where asmnt = 'pullups') pullups
from (
    select distinct on (id, asmnt) e.*
    from event e
    order by id, asmnt, timestamp desc
) e
group by id

Advertisement

Answer