Thursday, August 2, 2012

Mysql's THE GROUP_CONCAT() function

I must have written multiple custom code in Java to aggregate my results from a table. i.e. Assume a table like below.

Table A
-----------
name
-----------
Manisha
Nilanjan
Nima

To display (Manisha, Nilanjan, Nima ) I always use to aggregate all the values, using some sort of loop. With mysql group_concat function it can be done very easily.

SELECT GROUP_CONCAT(name) As Name FROM A;

Then the output will be:
name
----
Manisha, Nilanjan, Nima

We can also use some format of GROUP_CONCAT(). Like

SELECT GROUP_CONCAT( Language SEPARATOR ‘-’)-> It will use ‘-’ instead of ‘,’
SELECT GROUP_CONCAT( Language ORDER BY Language DESC ) -> To change the order and shorting output

Another thing to remember that GROUP_CONCAT() ignores NULL values. Group_Concat function can be used in join scenarios too. i.e.

Employee_TYPE Table
--------------------
Id | Desc
1        |  1 | FT  
2        |  2 | PT  


Employee table
---------------
ID | Name
1  | Manisha
1  | Nilanjan
2  | Nima

And to display something like below group_concat can be used too.
ID   | Desc | Name
   1 |  FT  |  Manisha, Nilanjan
   2 |  PT  |  Nima 

SELECT Employee_Type.*, (
    SELECT GROUP_CONCAT(Name SEPARATOR ',')
    FROM Employee
    WHERE Employee_Type.id = Employee.person_id
) as employee
FROM Employee_Type
   
Cool isn't it. 
Manisha 

No comments:

Post a Comment