I’ll show you the need of concatenating rows Into single column using real world example. Consider a situation where users place orders on items in a shop. So inorder to store the required details we need 3 tables user,orders and order_items as shown in the tables below.

 

User Tables
id	user_id	full_name	username
1	1	Adam	adam123
2	2	David	david123

 

Orders Tables
id	order_id	user_id	order_date
1	ORD001	1	10-10-2011
2	ORD002	2	12-10-2011
3	ORD003	2	12-10-2011

 

Order Items Tables
id	order_id	item_name	price
1	1	Nokia N9 Phone	$500.00
2	1	Nokia C3	$150.00
3	2	Nokia X5-01	$650.00
4	2	Nokia X3	$450.00
5	3	Nokia X5	$450.00

 

Now let’s assume clients requirement is to show list of users details and their order numbers in a list as shown in example below.

 

Username	Full Name	Orders Placed
adam123	Adam	ORD001
david123	David	ORD002 , ORD003

 

Their are two possible ways to create this list as mentioned above.

We can use a inner join between user and orders tables and get a result like the one shown below.

select username as Username,full_name as FullName,order_id as OrdersPlaced from user inner join orders on orders.user_id=users.user_id

 

Username	Full Name	Orders Placed
adam123	Adam	1
david123	David	2
david123	David	3

 

In this method we cannot directly get the required result. As you can see the username and full name is been repeated unnecessarily. So we have to go through this resultset using php and prepare with comma seperated order id’s in a single column.

We can directly get the required list using mysql Group Concat function. In the next section I’ll show you how to do this with complete syntax.

Group Concat function can be used to concat values of a single column in multiple rows to a single column of single row. In our case this will concat order ids in many rows to a single order id column.

select username as Username,full_name as FullName, GROUP_CONCAT(orders.order_id ORDER BY orders.order_id ASC SEPARATOR ',') as OrdersPlaced from user inner join orders on orders.user_id=user.user_id group by user.user_id

• In GROUP_CONCAT finction first you have to define the column you want to concat (orders.order_id). Concating integer valued column did not work. So I used a column of data type varchar
• Then use the order by clause to order the results as you want.
• Then use the SEPERATOR keyword and define the separator you want inside single quotes( Ex : ‘,’ or ‘-‘ …)
• Finally use the group by clause to group the multiple rows. You can use any column which has same values for grouping.