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?
Related Topics
How to Replace multiple Texts at the same Time
Tutorial | 0 Comments
CSS: Colorize Table Rows Alternately only with CSS
Tutorial | 0 Comments
XLS and XLSX: Maximum Number of Columns and Rows
Info | 2 Comments
SQLite: String and Column Concatenation using SQLite
Info | 0 Comments
PHP: Colorize Table Rows Alternately with PHP
Tutorial | 0 Comments
MySQL: Change minimum word length for full text search
Tip | 1 Comment
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.
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":
If you would like to determine a custom delimiter, you can define a string behind SEPARATOR:
If you would like like to sort your result, you can use ORDER BY <column> ASC or DESC:
Double values can be removed using DISTINCT:
If you would like to read out more than one user at the same time, you can work with GROUP BY:
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