SQL distinct values per group – how to “group by” and get a list of distinct values per group?

I need to do a bigquery/standard SQL statement (or a series of statements) that does “group by”, and returns a list of distinct values for each group (not count).

for example for tables with headers H1,H2

H1 H2  
--|--
a  a   
a  b
a  c
b  x
b  x
b  y

the expected result from “group by” H1:

a->a,b,c
b->x,y

Answer

If you’r using array_agg dont forget to use DISTINCT

select h1, array_agg(DISTINCT h2)
from t
group by h1;