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:
x
| 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);
Advertisement
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