Skip to content
Advertisement

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:

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:

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

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:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement