Few days ago, I got a mail from one of my friend. The mail was asking a question like below:-

Answer the following question within 10 sec and without the help of a calculator.

 

If 1 = 5, 2 = 25, 3 = 625, 4 = 390625, then 5 = ?

 

To know the answer Click here or

Skype Me™!

 

I asked this question to some other friends. They also replied the wrong answer. The answer is very simple. But sometimes we make complex and wrong solutions of simple problems. This happens due to lack of proper understanding the ‘system’.

 

Here is similar problem, which occurs frequently in our day to day programming.

 

Let you have team_member table data like below:-

id team_id fname initials last
1 1 John Smith
2 1 Irin E Adler
3 1 Michael Angelo
4 2 Alan Border
5 2 W J Akram

 

Now you want a report, which will show each team members in a row.

 

The simple and optimal solution is to use MySQL GROUP_CONCAT() function. MySQL GROUP_CONCAT() aggregate function is a small handy tool to solve this type of problem. This will simplify big efforts to a small piece of work. To see the above output using the power of MySQL GROUP_CONCAT() aggregate function, use the following query:

 

mysql > SELECT  team_id AS Team, GROUP_CONCAT( CONCAT( last, ', ',    fname, ' ', IFNULL('',initials))  ORDER BY last ASC SEPARATOR '; ' ) AS Members FROM  team_member GROUP BY team_id ORDER BY team_id ASC;

 

The output will be like below:-

Team Members
1 Adler, Irin E; Angelo Michael; Smith, John
2 Border, Alan; Akram, W J

 

MySQL GROUP_CONCAT() aggregate function is available from version 4.1.

 

To learn more, have a look at MySQL reference manual. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

You also must know about the MySQL global variable group_concat_max_len. There was a bug regarding this variable which was fixed in a later version 5.0.19. More …

 

 

 

The article was also published in my blog in 2007.

Add new comment