I have a dataset that looks like this:
id | test_id
---+--------
1 | a
1 | b
1 | u
2 | a
2 | u
3 | a
3 | b
3 | u
And I would like to roll it up into a new table such that the test_id's are the column names (along with id) and the rows are either 1 or 0s depending if the test was given to that id, like so
id | a | b | u
---+---+---+--
1 | 1 | 1 | 1
2 | 1 | 0 | 1
3 | 1 | 1 | 1
Is there a way in Postgres that I can rearrange the table such as this?
If the number of possible test_id is fixed and known the easiest way to do this is to use conditional expressions like this:
select
id,
max(case when test_id = 'a' then 1 else 0 end) as a,
max(case when test_id = 'b' then 1 else 0 end) as b,
max(case when test_id = 'u' then 1 else 0 end) as u
from your_table
group by id
order by id
If the test_id values are unknown and can vary then you need to use dynamic sql to generate the query.