Horizontal looper in one mysql table

Question

I have a mysql table:

Each user have 4 emails:

 id user_Id email
 1  11      [email protected]
 2  11      [email protected]
 3  11      [email protected]
 4  11      [email protected]
 5  12      [email protected]
 6  12      [email protected]
 7  12      [email protected]
 8  12      [email protected]
 9  13      [email protected]
 10 13      [email protected]
 11 13      [email protected]
 12 13      [email protected]

How to sort the row result horizontally?

Like that:

 user_Id   email1           email2           email3             email4
 11        [email protected] [email protected] [email protected]   [email protected]
 12        [email protected] [email protected] [email protected]   [email protected]
 13        [email protected] [email protected] [email protected]   [email protected]

have a look at pivot tables in mysql – Lelio Faieta Apr 18 '18 at 09:07 — Lelio Faieta, Apr 18 '18 at 09:07

score 2 · Answer 1 · answered Apr 18 '18 at 10:49

2

If your emails are limited to 4 then you can follow this

select t1.user_id,
    (select email from test where test.user_id = t1.user_id limit 0,1) as email1,
    (select email from test where test.user_id = t1.user_id limit 1,1) as email2,
    (select email from test where test.user_id = t1.user_id limit 2,1) as email3,
    (select email from test where test.user_id = t1.user_id limit 3,1) as email4
from test t1
group by t1.user_id;

answered Apr 18 '18 at 10:49

DEarTh

975
1
7
18

Perfect, pleased to see this answer. :) – Pupil Apr 18 '18 at 10:55
@jeanpaul, if this answer works for you, please mark this answer as solution. – Pupil Apr 18 '18 at 10:56

score 1 · Answer 2 · answered Apr 18 '18 at 09:05

1

select user_Id , GROUP_CONCAT(email ORDER BY email) from tab
group by user_Id

answered Apr 18 '18 at 09:05

starko

1,150
11
26

this way you will get only one column with all the emails and not for with one each – Lelio Faieta Apr 18 '18 at 09:07

Horizontal looper in one mysql table

2 Answers2