3

SQL fiddle: http://sqlfiddle.com/#!9/e7f72/2

Assume a table called testt has 10 records in it (id is not null auto increment). If I were to do

SELECT GROUP_CONCAT(id) FROM testt

I would expect the results to look like

1,2,3,4,5,6,7,8,9,10

How could I get the results to look like this:

1,2

3,4

5,6

7,8

9,10

ekad
  • 14,436
  • 26
  • 44
  • 46
user2278120
  • 623
  • 2
  • 9
  • 22

2 Answers2

5

You would need to group by a function of the id. Something like this:

select group_concat(id order by id)
from testt
group by floor((id - 1) / 2)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is exactly what I needed, thank you! I will accept it as the answer in 9 minutes when SO let's me. Care to add an explanation? – user2278120 Jan 13 '16 at 00:22
  • I applied this to some of my live tables, and I realized this solution only works if there are no gaps in your Id column. If you have id's 1-5 then 15-30, it does not function properly. I'll edit my question with the answer when I figure out a solution. – user2278120 Jan 13 '16 at 20:41
  • @user2278120 . . . You should ask another question, with an explicit mention of the gaps (and even a reference back to this question). – Gordon Linoff Jan 14 '16 at 02:14
  • @Gordon Linoff I already figured it out. Are you sure I should create a question I already know the answer to? – user2278120 Jan 14 '16 at 15:39
  • @user2278120 . . . My point is not to ask a question in a comment, but as a question (this is a grey area . . . little questions are appropriate for comments). – Gordon Linoff Jan 15 '16 at 22:36
  • @user2278120 you are perfectly welcome to ask and self-answer the new question if you already have the answer and your question is unique to Stack Overflow. – mickmackusa Nov 10 '21 at 08:22
4

In case anyone should run into this problem, but need it work on an id column that is not sequential, here is the solution.

In the below query test is the table name with the non sequential id column. And Dbname is optional if you already have your db selected. 

SELECT  
   GROUP_CONCAT(id ORDER BY id ASC) AS "ids" 
FROM  
(  
    SELECT  
    @row := @row +1 AS rownum, id
    FROM  
    (  
    SELECT @row :=0 
    ) r, Dbname.test 
) ranked  
GROUP BY 
    FLOOR((rownum - 1) / 2);

If you have a table that looks like this: 

id
1
2
3
4
10
15
17
18
20
21
23
25
32
55
105
1011
1012
1013
1014
1111
1112
1113
1114
1115
1116
1117
1118
1119
2001
2002
2003

The above query will give the results like this: 

ids
1,2
3,4
10,15
17,18
20,21
23,25
32,55
105,1011
1012,1013
1014,1111
1112,1113
1114,1115
1116,1117
1118,1119
2001,2002
2003

And finally if you changed this line

FLOOR((rownum - 1) / 2);

to 

 FLOOR((rownum - 1) / 3);

The results would like this:

ids
1,2,3
4,10,15
17,18,20
21,23,25
32,55,105
1011,1012,1013
1014,1111,1112
1113,1114,1115
1116,1117,1118
1119,2001,2002
2003

Hope that helps someone.

user2278120
  • 623
  • 2
  • 9
  • 22