Mysql data displaying multiple rows instead of one row

Question

I would like to know how to display the data above into one row based on the field value i did a case select but it's coming up as the following image

(SELECT lead_id,
 case lead_id
 case field_number when 1  then value  end as firstname

 case field_number when 1 then value end as firstname,
 case field_number when 2 then value end as lastname,
 case field_number when 10 then value end as email,

sagi · Accepted Answer · 2016-03-28 13:17:30Z

2

You are trying to do it with conditional aggregation (meaning you are missing a group by clause an a max/min function) :

SELECT lead_id,
       MAX(CASE WHEN field_number = 1 then value end) as firstname,
       MAX(CASE WHEN field_number = 2 then value end) as lastname,
       MAX(CASE WHEN field_number = 10 then value end) as email
FROM YourTable
GROUP BY lead_id

answered Mar 28, 2016 at 13:17

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lightness Races in Orbit Over a year ago

Worth noting that this table layout goes against the grain entirely.

Master Page Over a year ago

Thanks sagi this was very useful i knew i was missing something about the lead id.

Master Page Over a year ago

BTW i did not create this database nor the table and you right Barry TheHatchet terrible disign.

sagi Over a year ago

No problem @MasterPage :)

Collectives™ on Stack Overflow

Mysql data displaying multiple rows instead of one row

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related