CODEWITHSUNDEEP
mysqlaggregate-functions
Kamil
Kamil

Reputation: 13931

Aggregate function in MySQL - list (like LISTAGG in Oracle)

I need function, that returns list of strings.

I have data in table like this:

Id    MyString
------------------------
 1    First
 2    Second
 3    Third
 4    Fourth

I need function like this (something like this works in oracle):

select LISTAGG(MyString, ', ') as myList where id < 4

That returns something like this:

myList
------------------------
First, Second, Third

Any ideas?

Upvotes: 40

Views: 69712

Answers (2)

alumarcu
alumarcu

Reputation: 131

As of MySQL 5.7.22 you can also use two JSON aggregation functions: JSON_ARRAYAGG or JSON_OBJECTAGG. You can combine these together with MySQL's JSON functions to get results aggregated as JSON. Unlike GROUP_CONCAT, there is no MySQL configuration parameter which would limit the size of the returned value, other than max_allowed_packet (which affects all queries).

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

You're looking for GROUP_CONCAT()

Try this:

select group_concat(MyString separator ', ') as myList from table
where id < 4

Of course, you can group by the results.

Upvotes: 74

Related Questions