mysql merge three different values from different rows

Question

I haven't found an answer specific enough for exactly what I'm looking for, so I'm hoping this isn't a duplicate question.

I am trying to create a list of full names in a results list that looks like this:

id  | form_id | element_label | element_value | group_id
245   10        1               John            34
245   10        2               A               34
245   10        3               Doe             34
245   10        1               George          35
245   10        2               C               35
245   10        3               Washington      35

This is from my query: SELECT * FROM jm3_formmaker_submits WHERE form_id = '10' AND element_label <4

The result I want would be:

group_id | full_name
34         John A Doe
35         George C Washington

Thank you so very much for your help.

ypercubeᵀᴹ · Accepted Answer · 2013-06-25 18:24:09Z

2

You should look on GROUP_CONCAT() function.

It will be something like this:

SELECT group_id, 
       GROUP_CONCAT(element_value 
                    ORDER BY element_label
                    SEPARATOR ' ')
         AS full_name 
FROM jm3_formmaker_submits 
WHERE form_id = 10 
  AND element_label < 4 
GROUP BY group_id;

edited Jun 25, 2013 at 18:24

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Jun 25, 2013 at 18:07

drull

5291 gold badge3 silver badges10 bronze badges

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

2 Comments

thecommonthread Over a year ago

Thanks dull; appreciate the answer.

ypercubeᵀᴹ Over a year ago

No need for REPLACE(). The GROUP_CONCAT() function has SEPARATOR.

David Jashi · Accepted Answer · 2013-06-25 18:09:24Z

0

This one will give you more control on string structure (I guess you will want to separate them with spaces, won't you?):

select group_id,concat(a,' ',b,' ',c) from
(select group_id,element_value as a from jm3_formmaker_submits where element_label=1) as first_name,
(select group_id,element_value as b from jm3_formmaker_submits where element_label=2) as middle_name,
(select group_id,element_value as c from jm3_formmaker_submits where element_label=3) as last__name
where
middle_name.group_id=first_name.group_id and
last__name.group_id=first_name.group_id

answered Jun 25, 2013 at 18:09

David Jashi

4,5091 gold badge23 silver badges26 bronze badges

2 Comments

drull Over a year ago

Better to use REPLACE() mysql function.

David Jashi Over a year ago

@drull where in particular?

Collectives™ on Stack Overflow

mysql merge three different values from different rows

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related