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
>Solution :
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
)