Can I concatenate multiple MySQL rows into one field?

Try this:

DECLARE @Hobbies NVARCHAR(200) = ' '

SELECT @Hobbies = @Hobbies + hobbies + ',' FROM peoples_hobbies WHERE person_id = 5;

TL;DR;

set @sql='';
set @result='';
set @separator=' union \r\n';
SELECT
@sql:=concat('select ''',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME ,''' as col_name,',
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH ,' as def_len ,' ,
'MAX(CHAR_LENGTH(',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME , '))as max_char_len',
' FROM ',
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
) as sql_piece, if(@result:=if(@result='',@sql,concat(@result,@separator,@sql)),'','') as dummy
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE like '%char%'
and INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='xxx'
and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='yyy';
select @result;

It is late but will helpfull for those who are searching "concatenate multiple MySQL rows into one field using pivot table" :)

Query:

SELECT pm.id, pm.name, GROUP_CONCAT(c.name) as channel_names
FROM payment_methods pm
LEFT JOIN payment_methods_channels_pivot pmcp ON pmcp.payment_method_id = pm.id
LEFT JOIN channels c ON c.id = pmcp.channel_id
GROUP BY pm.id


Tables

payment_methods
id | name
1 | PayPal

channels
id | name
1 | Google
2 | Faceook

payment_methods_channels_pivot
payment_method_id | channel_id
1 | 1
1 | 2

Output:

[![enter image description here][1]][1]


[1]:

In sql server use string_agg to pivot a row field values into a column:

select string_agg(field1, ', ') a FROM mytable

or

select string_agg(field1, ', ') within group (order by field1 dsc) a FROM mytable group by field2

Another interesting example in this case -

Following is the structure of the sample table `people_hobbies` -


```
DESCRIBE people_hobbies;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| ppl_id | int unsigned | YES | MUL | NULL | |
| name | varchar(200) | YES | | NULL | |
| hby_id | int unsigned | YES | MUL | NULL | |
| hobbies | varchar(50) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+

```

The table is populated as follows -

```
SELECT * FROM people_hobbies;
+----+--------+-----------------+--------+-----------+
| id | ppl_id | name | hby_id | hobbies |
+----+--------+-----------------+--------+-----------+
| 1 | 1 | Shriya Jain | 1 | reading |
| 2 | 4 | Shirley Setia | 4 | coding |
| 3 | 2 | Varsha Tripathi | 7 | gardening |
| 4 | 3 | Diya Ghosh | 2 | fishing |
| 5 | 4 | Shirley Setia | 3 | gaming |
| 6 | 1 | Shriya Jain | 6 | cycling |
| 7 | 2 | Varsha Tripathi | 1 | reading |
| 8 | 3 | Diya Ghosh | 5 | shopping |
| 9 | 3 | Diya Ghosh | 4 | coding |
| 10 | 4 | Shirley Setia | 1 | reading |
| 11 | 1 | Shriya Jain | 4 | coding |
| 12 | 1 | Shriya Jain | 3 | gaming |
| 13 | 4 | Shirley Setia | 2 | fishing |
| 14 | 4 | Shirley Setia | 7 | gardening |
| 15 | 2 | Varsha Tripathi | 3 | gaming |
| 16 | 2 | Varsha Tripathi | 2 | fishing |
| 17 | 1 | Shriya Jain | 5 | shopping |
| 18 | 1 | Shriya Jain | 7 | gardening |
| 19 | 3 | Diya Ghosh | 1 | reading |
| 20 | 4 | Shirley Setia | 5 | shopping |
+----+--------+-----------------+--------+-----------+
```

Now, a table `hobby_list` is generated having the list of all people and a list of each person's hobbies with each hobby in a new line -

```
CREATE TABLE hobby_list AS
-> SELECT ppl_id, name,
-> GROUP_CONCAT(hobbies ORDER BY hby_id SEPARATOR "\n")
-> AS hobbies
-> FROM people_hobbies
-> GROUP BY ppl_id
-> ORDER BY ppl_id;
```

```
SELECT * FROM hobby_list;
```

[![CONCAT_GROUP()][1]][1]


[1]:

Here, my intension was to apply string concatenation without using group_concat() function:

Set @concatHobbies = '';
SELECT TRIM(LEADING ', ' FROM T.hobbies ) FROM
(
select
Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
from peoples_hobbies
)T
Order by Id DESC
LIMIT 1

Here

select
Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
from peoples_hobbies

will return

Id hobbies
1 , shopping
2 , shopping, fishing
3 , shopping, fishing, coding

Now our expected result is at third position. So I am taking the Last row by using


Order by Id DESC
LIMIT 1


Then I am also removing the First ', ' from my string

TRIM(LEADING ', ' FROM T.hobbies )

Use **GROUP_CONCAT**:

SELECT GROUP_CONCAT(hobbies) FROM peoples_hobbies WHERE person_id = 5;

I found myself wanting to **select multiple, individual rows**—instead of a group—and concatenate on a certain field.

### Given:

Let's say you have a table of product ids and their names and prices:



+------------+--------------------+-------+
| product_id | name | price |
+------------+--------------------+-------+
| 13 | Double Double | 5 |
| 14 | Neapolitan Shake | 2 |
| 15 | Animal Style Fries | 3 |
| 16 | Root Beer | 2 |
| 17 | Lame T-Shirt | 15 |
+------------+--------------------+-------+

Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.

Your hungry-hippo user selects `13, 15, 16`. No dessert for her today...

### Find:

A way to summarize your user's order in one line, with pure mysql.

### Solution:

Use `GROUP_CONCAT` with the [the `IN` clause](

[To see links please register here]

):

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);

Which outputs:


+------------------------------------------------+
| order_summary |
+------------------------------------------------+
| Double Double + Animal Style Fries + Root Beer |
+------------------------------------------------+

### Bonus Solution:

If you want the total price too, toss in [`SUM()`](

[To see links please register here]

):

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);
+------------------------------------------------+-------+
| order_summary | total |
+------------------------------------------------+-------+
| Double Double + Animal Style Fries + Root Beer | 10 |
+------------------------------------------------+-------+