How do I concatenate SQL queries into a string?

Question

How can I get individual values from a column and concatenate them into one row? Thank.

For example, I have a column like

fruit_name
------
apple
apple
apple
banana
orange
orange
.
.
.

I want to create the result as apple, banana, orange...

Here is the query I tried, but it will give the result apple, apple, apple, banana...

SELECT LISTAGG(fruit_name,', ') WITHIN GROUP (ORDER BY fruit_name) "fruit_List" FROM FRUIT_TABLE;

I do not know how to make the result perfect.

+3

Natsuki_Kato Jul 24 15 at 6:07

4 answers

Try this query,

SELECT GROUP_CONCAT(DISTINCT fruit_name) FROM FRUIT_TABLE ORDER BY fruit_name;

+1

Priyank Jul 24 15 at 6:15 am

You can use the following query using GROUP_CONCAT

as LISTAGG

not available in mysql:

SELECT GROUP_CONCAT(DISTINCT fruit_name ORDER BY fruit_name ASC SEPARATOR ',') as fruit_List
FROM FRUIT_TABLE

GROUP_CONCAT

has DISTINCT

and ORDER BY

attributes included

+1

Nikhil Batra Jul 24 15 at 6:15 am

edit: (my first version was clearly wrong!)

select group_concat(fn) fruit_list
from (select fruit_name fn 
         from fruit_table 
         group by fruit_name) fruits

It works now, see here http://sqlfiddle.com/#!9/bfbf4/2 , but Nikhil Batra's solution is by far the best choice!

0

cars10m Jul 24 15 at 6:15 am

Tim biegeleisen · Accepted Answer · 2015-07-24T06:14:20+0000

You can use MySQL function GROUP_CONCAT()

to solve your problem:

SELECT GROUP_CONCAT(t.fn)
FROM
(
    SELECT fruit_name fn
    FROM FRUIT_TABLE
    GROUP BY fruit_name
) t

The default delimiter in GROUP_CONCAT

is the comma that you specified in your original problem.

Update:

I do justice to this solution by @ cars10 who pointed out that everyone initially got it wrong.