Tareq Tareq -3 years ago 110
MySQL Question

mysql separating group_concat result in multiple rows

My table is as below:

create table testing(
bnum varchar(7)
);


and here is the values:

insert into testing values
('0547366'),
('0547367'),
('0547368'),
('0547369'),
('0547370'),
('0547371'),
('0547372'),
('0547373'),
('0547374'),
('0547375'),
('0547376');


I used the below query:

select group_concat(bnum) as nums from testing;


and getting the following result:

+============
nums
+============
0547366,0547367,0547368,0547369,0547370,0547371,0547372,0547373,0547374,0547375,0547376
+============


But I would like to have the following result where I need multiple sets of numbers where 3 numbers in each set separated by comma:

+============
nums
+============
0547366,0547367,0547368
+============
0547369,0547370,0547371
+============
0547372,0547373,0547374
+============
0547375,0547376
+============


What will the query? Please help !!!

Answer Source
select GROUP_CONCAT(bnum),@cnt := @cnt+1,concat('a',@cnt1),case when MOD(@cnt,3)=0 then @cnt1:=@cnt1+1 end,@cnt1 from testing,(SELECT @cnt :=0,@cnt1:=0)z GROUP BY @CNT1 ORDER BY@CNT;

You can try above query.

Fiddle Demo is here.

Hope this will help you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download