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

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

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
)