How to separate group_concat result in multiple rows using MySQL?

Question

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
+============

How can I write this query?

Actually my result set is too long and I wanna separate the result where there is 3 numbers in each set. My actual table is more complex so I simplified the table so that everybody can better understand my problem. — Tareq, Aug 16 '17 at 06:51
I can handle it in application code but wanna interested in query first. — Tareq, Aug 16 '17 at 06:58

score 1 · Accepted Answer · edited Sep 01 '17 at 21:35

1

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.

edited Sep 01 '17 at 21:35

halfer

19,824
17
99
186

answered Aug 16 '17 at 07:14

Sagar Gangwal

7,544
3
24
38

@Tareq keep it up ..!! – Sagar Gangwal Aug 16 '17 at 07:36

How to separate group_concat result in multiple rows using MySQL?

1 Answers1