How to: For each unique id, for each unique version, grab the best score and organize it into a table

Question

Just wanted to preface this by saying while I do have a basic understanding, I am still fairly new to using Bigquery tables and sql statements in general.

I am trying to make a new view out of a query that grabs all of the best test scores for each version by each employee:

select emp_id,version,max(score) as score from `project.dataset.table` where type = 'assessment_test' group by version,emp_id order by emp_id

I'd like to take the results of that query, and make a new table comprised of employee id's with a column for each versions best score for that rows emp_id. I know that I can manually make a table for each version by including a "where version = a", "where version = b", etc.... and then joining all of the tables at the end but that doesn't seem like the most elegant solution plus there is about 20 different versions in total.

Is there a way to programmatically create a column for each unique version or at the very least use my initial query as maybe a subquery and just reference it, something like this:

with a as (
  select id,version,max(score) as score 
  from `project.dataset.table` 
  where type = 'assessment_test' and version is not null and score is not null and id is not null 
  group by version,id 
  order by id),

version_a as (select score from a where version = 'version_a')
version_b as (select score from a where version = 'version_b')
version_c as (select score from a where version = 'version_c')

select 
  a.id as id,
  version_a.score as version_a,
  version_b.score as version_b,
  version_c.score as version_c
from 
a,
version_a, 
version_b,
version_c

Example Picture: left table is example data, right table is expected output

Example Data:

id	version	score
1	a	88
1	b	93
1	c	92
2	a	89
2	b	99
2	c	78
3	a	95
3	b	83
3	c	89
4	a	90
4	b	90
4	c	86
5	a	82
5	b	78
5	c	98
1	a	79
1	b	97
1	c	77
2	a	100
2	b	96
2	c	85
3	a	83
3	b	87
3	c	96
4	a	84
4	b	80
4	c	77
5	a	95
5	b	77

Expected Output:

id	a score	b score	c score
1	88	97	92
2	100	99	85
3	95	87	96
4	90	90	86
5	95	78	98

Thanks in advance and feel free to ask any clarifying questions

Answer 1

Use below approach

select * from your_table
pivot (max(score) score for version in ('a', 'b', 'c'))      

if applied to sample data in your question - output is

In case if versions is not known in advance - use below

execute immediate (select '''
select * from your_table
pivot (max(score) score for version in (''' || string_agg(distinct "'" || version || "'") || "))"
from your_table
)