Aggregate multiple columns into array

Question

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.

Answer 1

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