Need sql query


I’m very new to SQL and databases. I need a query for the following: (I’m sure google would have found the answer, but I could not really frame the sentence for the task I’m looking for. Also, please let me know how do I search in google for such tasks – so that I can try it myself in future).

Assume I have a table like:

Sl No Fruit Grown in states
1 Apple KA
2 Orange AP
3 Banana TN
4 Jackfruit MH
5 Mango MP
6 Jackfruit MP
7 Banana AP
8 Mango KA
9 Banana TN
10 Apple MH
11 Jackfruit AP
12 Orange MH
13 Mango KA
14 Apple TN
15 Banana MP
16 Banana MH
17 Mango KA
18 Orange MP
19 Jackfruit AP
20 Apple TN

From the above table, I want a SQL query which will list me the unique fruits and the states in which they are grown, like: (comma not necessary - i can add it using perl)

Apple: KA, MH, TN
Banana: TN, AP, MP, MH
Jackfruit: MH,MP,AP
Mango: MP, KA
Orange: AP,MH,MP

Thanks in advance for the help,

Try something like this

SELECT fruit
     , GROUP_CONCAT(state) AS states
  FROM yourtable
    BY fruit
    BY fruit;

Hi SirAdrian,

Thanks a lot for the reply. It works!!.