How can I create the array using SQL query in presto/hive?-CodePudding

There are two records:

Type	name
Fruit	Apple
Fruit	Orange

I need below output using sql query in presto:

Type	name
Fruit	["Apple","Orange"]

How can I write the sql query to get above table?

CodePudding user response：

Aggregate function array_agg is designed especially for this purpose:

--sample data
WITH dataset(type, name) AS (
    VALUES ('Fruit', 'Apple'),
           ('Fruit', 'Orange')
)

-- sample query
SELECT type, array_agg(name) name
FROM dataset
group by type

Output:

type	name
Fruit	[Apple, Orange]