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.
If for some reason preserving null is a must for you - you can use below trick - but note - the output is not an array and rather string
select user,
translate(format('%t', array_agg(struct(score_1, score_2, score_3))), '()', '') scores
from your_table
group by user
or even simpler
select user,
format('%t', array_concat_agg([score_1,score_2,score_3])) scores
from your_table
group by user
with same output for both above
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
array_aggfirst and thenarray_concat, but you need to decide how you want to handlenulls