Edamame Edamame - 2 months ago 12
SQL Question

Impala/SQL: group by - aggregate a field by creating a list of all values

I have a table below:

id | animal | team
-------------------------
1 | dog | A
2 | cat | A
3 | cow | A
4 | bird | B
5 | rat | B
6 | goat | C


I want to group by team, and aggregate the animals in a team to a list. i.e. the output should look like:

team | animal_list
--------------------------
A | [dog, cat, cow]
B | [bird, rat]
C | [goat]


Can this be achieved by Impala/SQL query?

Answer

Please use this query.

select team, concat("[", group_concat(animal), "]") as animal_list from   anmials group by team

You can see the result output here : http://sqlfiddle.com/#!9/1b03f/3