user19460554
user19460554

Reputation:

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

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?

Upvotes: 1

Views: 564

Answers (1)

Guru Stron
Guru Stron

Reputation: 143088

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]

Upvotes: 1

Related Questions