00 Votes

MySQL: CONCAT multiple Rows

Question by Guest | 2014-09-22 at 22:48

I would like to read out the database results from several fields combined into only one field.

If those fields are available within only one row of my table, this is no problem and I can simply use CONCAT. For example, when reading out the first name and last name of a person, I can just use the following query:

SELECT CONCAT(firstname, ' ', lastname) AS fullname
FROM users
WHERE id = 1

Although "firstname" and "lastname" are two different fields in my database, they are shown as a common field "fullname" in the result.

Now, I would like to use a CONCAT over multiple rows (and not multiple fields from only one row) to be able to combine the result out of some fields located under each other in my table.

For example, each user stored in my table has assigned an arbitrary number of properties. Those properties are stored in the table "props" where there is for each user and property an individual line (for more information about the property). To read out the properties for a specific user, you can use the following query:

SELECT prop 
FROM props
WHERE user_id = 1

The result of such a query could look like this (three result lines):

A
B
C

But I would prefer the following arrangement of the resolved in only one line and field:

A, B, C

Is there any possibility to achieve such a result in my escort at?

ReplyPositiveNegative
0Best Answer0 Votes

Just have a look at GROUP_CONCAT. With this, it should be possible to achieve the results, you are looking for.

With the following query, you get a result in the form "A,B,C":

SELECT GROUP_CONCAT(col) 
FROM props 
WHERE user_id = 1

If you would like to determine a custom delimiter, you can define a string behind SEPARATOR:

SELECT GROUP_CONCAT(col SEPARATOR ', ') 
FROM props 
WHERE user_id = 1

If you would like like to sort your result, you can use ORDER BY <column> ASC or DESC:

SELECT GROUP_CONCAT(col ORDER BY col ASC SEPARATOR ',') 
FROM props 
WHERE user_id = 1

Double values can be removed using DISTINCT:

SELECT GROUP_CONCAT(DISTINCT col SEPARATOR ', ') 
FROM props 
WHERE user_id = 1

If you would like to read out more than one user at the same time, you can work with GROUP BY:

SELECT user_id, GROUP_CONCAT(col SEPARATOR ', ') 
FROM props 
GROUP_BY user_id

Important if you are expecting long results: MySQL knows the variable group_concat_max_len, which is mostly set to 1024 bytes and limiting the length of a GROUP_CONCAT-field. Whenever having results longer as this limit, the result is cut. You can adapt this value to your needs via your MySQL settings or by using "SET group_concat_max_len = NEWVALUE".
2014-09-25 at 07:26

ReplyPositive Negative
Reply

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.