sqlgoogle-bigqueryarray-agg

Aggregate multiple columns into array

Suppose I have a table like the following

user score_1 score_2 score_3
1 100 80 100
1 80 null 80
2 95 90 65

I would like to aggregate the 3 scores columns into an array. The result will look like (the order does not matter)

user scores
1 [100,80,100,80,null,80]
2 [95,90,65]

I know I can take union of (user, score1), (user, score2) and (user,score3) and then do array_agg on top of that. I am wondering if there is a more efficient way.

Solution

  • Consider below

    select user, array_agg(score) scores
from your_table
unpivot (score for col in (score_1, score_2, score_3))
group by user

    if applied to sample data in your question - output is

    enter image description here