multiple records per row query

Question

I have a table (in mysql) like this:

TABLE1

Id   Name   Age 
---------------
1    John   22
2    Mary   17
3    Peter  21
4    Agnes  34
5    Steve  14
6    Bart   26
7    Bob    32
8    Vince  18
...

What I am looking for is a SELECT statement, where I can get 4 records in a row. I mean, the result of the select statement would be:

Id1 Name1 Age1  Id2 Name2 Age2 Id3 Name3 Age3 Id4 Name4 Age4
-----------------------------------------------------------
1   John  22    2   Mary  17   3   Peter 21   4   Agnes 34
5   Steve 14    6   Bart  26   7   Bob   32   8   Vince 18
...

I guess it would be like a pivot... Is this possible? If it is, then how can I achieve it? I need to populate a report by showing 4 records on a row, so I would like to be able to do it from a datasource that returns this exact structure. So on first band/row there will be

rec1,rec2,rec3,rec4

then on second row:

rec5,rec6,rec7,rec8

and so on.

My first idea was to merge 4 queries that return every 5th record starting with 1,2,3,4 but I'm not exactly sure... Can you help?

This sounds like a formatting issue in your view, not a SQL issue. When you're displaying your records, why not write a loop that checks if the record you're on is divisible by 4, and if so start a new "row"? — Sean, Apr 15 '16 at 02:02
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Apr 15 '16 at 02:03

score 0 · Answer 1 · answered Apr 15 '16 at 02:02

You can do this with arithmetic on the id and group by:

select (case when id % 4 = 1 then id end) as id1,
       (case when id % 4 = 1 then name end) as name1,
       (case when id % 4 = 1 then age end) as age1,
       (case when id % 4 = 2 then id end) as id2,
       (case when id % 4 = 2 then name end) as name2,
       (case when id % 4 = 2 then age end) as age2,
       (case when id % 4 = 3 then id end) as id3,
       (case when id % 4 = 3 then name end) as name3,
       (case when id % 4 = 3 then age end) as age3,
       (case when id % 4 = 0 then id end) as id4,
       (case when id % 4 = 0 then name end) as name4,
       (case when id % 4 = 0 then age end) as age4
from t
group by floor((id - 1) / 4);

If the id doesn't increment without gaps, then you can generate one using:

from (select t.*, (@rn := @rn + 1) as seqnum
      from t cross join
           (select @rn := 0) params
      order by id
     ) t

multiple records per row query

1 Answers1