mysql concat column from two rows based on a column

Question

Have the following table which i am trying to concat two rows from the 'value' column into one

mysql> select * from table;
+-----+---------+----------+------------------------+
| id  | rsvp_id | field_id | value                  |
+-----+---------+----------+------------------------+
| 181 |      37 |        1 | First                  |
| 184 |      37 |        4 | Last                   |
| 187 |      37 |       10 |                        |
| 190 |      37 |       13 | spicegirls             |
| 193 |      37 |        7 | [email protected]         |
| 196 |      40 |        1 | Brian                  |
| 199 |      40 |        1 | Smith                  |
| 202 |      40 |        7 | Brian@test .com        |
| 205 |      40 |       10 | BBQ                    |
+-----+---------+----------+------------------------+
9 rows in set (0.00 sec)

Ideally i'd like to get the following result

rsvp_id  | value
========  ========
37         First Last
40         Brian Smith

The query only grabs the rows with field_id=1 then concats the value column and creates a new row with rsvp_id and the concat value.

Also, the field_id column right now and the 1 is an example, i'll have to figure out how to make it work so instead of 1 it takes the condition from a different table.

Basically the above are values for first name and last name. field_id is a foreign_key to a different table.

I've tried searching online and messing with it myself but i wasn't able to merge the two rows into one row.

Thank You.

something like: group_concat(value, ' ') where field_id in (1,4) group by rsvp_id — Paul Maxwell
– Paul Maxwell, Commented Nov 19, 2018 at 23:37
This is what i have so far: SELECT rsvp_id, GROUP_CONCAT(value SEPARATOR ' ') as n FROM registration_rsvp_field_value left join registration_rsvp_custom_field on (registration_rsvp_custom_field.id=registration_rsvp_field_value.field_id) left join registration_rsvp on (registration_rsvp.id=registration_rsvp_field_value.rsvp_id) WHERE field_id IN (1, 2) AND form_id=1 GROUP BY rsvp_id — Vincent
– Vincent, Commented Nov 21, 2018 at 0:16

johannes · Accepted Answer · 2018-11-19 23:37:49Z

3

You have to use grouping and then use GROUP_CONCAT.

Something like this (untested) might work:

   SELECT rsvp_id, GROUP_CONCAT(value SEPARATOR ' ')
     FROM t
    WHERE field_id = 1
 GROUP BY rsvp_id

See MySQL docs for details, also to learn about ordering of values etc: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat

answered Nov 19, 2018 at 23:37

johannes

16k3 gold badges47 silver badges57 bronze badges

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

2 Comments

Vincent Over a year ago

Hey, It seems this does exactly what i had before. the following query SELECT rsvp_id, GROUP_CONCAT(value SEPARATOR ' ') as n FROM registration_rsvp_field_value left join registration_rsvp_custom_field on (registration_rsvp_custom_field.id=registration_rsvp_field_value.field_id) left join registration_rsvp on (registration_rsvp.id=registration_rsvp_field_value.rsvp_id) WHERE field_id IN (1, 2) AND form_id=1 GROUP BY rsvp_id

Vincent Over a year ago

output something like this rsvp_id | n 37 | Mark 38 | Dan 39 | John if i remove the GROUP BY rsvp_id i only get one column with the first name concated

Collectives™ on Stack Overflow

mysql concat column from two rows based on a column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related