Horizontal looper in one mysql table

Question

I have a mysql table:

Each user have 4 emails:

 id user_Id email
 1  11      emailx@email.com
 2  11      emailc@email.com
 3  11      emaild@email.com
 4  11      emailv@email.com
 5  12      emailr@email.com
 6  12      emailb@email.com
 7  12      emailW@email.com
 8  12      emailT@email.com
 9  13      emailJ@email.com
 10 13      emailI@email.com
 11 13      emailL@email.com
 12 13      emailQ@email.com

How to sort the row result horizontally?

Like that:

 user_Id   email1           email2           email3             email4
 11        emailx@email.com emailc@email.com emaild@email.com   emailv@email.com
 12        emailr@email.com emailb@email.com emailW@email.com   emailT@email.com
 13        emailJ@email.com emailI@email.com emailL@email.com   emailQ@email.com

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