Help to concatenate a result...
What I need to do is take this query and this result: SELECT segfees FROM fsf; +-+ | segfees | +-+ | FS=5.00 | | AY=2.50 | | XF=1.75 | | ZP=3.20 | +-+ I make it just return ONE row like this: +-+ | segfees | +-+ | FS=5.00,AY=2.50,XF=1.75,ZP=3.20 | +-+ Any idea how to write a query to return this type of result? (I plan on using it as a subquery, that's why I only want one result) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help to concatenate a result...
Hi,
You can use select group_concat(segfees) from fsf;
The information bellow is from mysql manual, but you have to have mysql
4.1.x
=
*
|GROUP_CONCAT(/|expr|/)|
This function returns a string result with the concatenated
non-|NULL| values from a group. It returns |NULL| if there are no
non-|NULL| values. The full syntax is as follows:
GROUP_CONCAT([DISTINCT] /|expr|/ [,/|expr|/ ...]
[ORDER BY {/|unsigned_integer|/ | /|col_name|/ | /|expr|/}
[ASC | DESC] [,/|col_name|/ ...]]
[SEPARATOR /|str_val|/])
mysql *|SELECT student_name,|*
- *|GROUP_CONCAT(test_score)|*
- *|FROM student|*
- *|GROUP BY student_name;|*
Or:
mysql *|SELECT student_name,|*
- *|GROUP_CONCAT(DISTINCT test_score|*
- *|ORDER BY test_score DESC SEPARATOR ' ')|*
- *|FROM student|*
- *|GROUP BY student_name;|*
In MySQL, you can get the concatenated values of expression
combinations. You can eliminate duplicate values by using
|DISTINCT|. If you want to sort values in the result, you should
use |ORDER BY| clause. To sort in reverse order, add the |DESC|
(descending) keyword to the name of the column you are sorting by
in the |ORDER BY| clause. The default is ascending order; this may
be specified explicitly using the |ASC| keyword. |SEPARATOR| is
followed by the string value that should be inserted between
values of result. The default is a comma (‘|,|’). You can remove
the separator altogether by specifying |SEPARATOR ''|.
You can set a maximum allowed length with the
|group_concat_max_len| system variable. The syntax to do this at
runtime is as follows, where |val| is an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = val;
If a maximum length has been set, the result is truncated to this
maximum length.
=
Best regards,
Cristi
Cory @ SkyVantage wrote:
What I need to do is take this query and this result:
SELECT segfees FROM fsf;
+-+
| segfees |
+-+
| FS=5.00 |
| AY=2.50 |
| XF=1.75 |
| ZP=3.20 |
+-+
I make it just return ONE row like this:
+-+
| segfees |
+-+
| FS=5.00,AY=2.50,XF=1.75,ZP=3.20 |
+-+
Any idea how to write a query to return this type of result? (I plan on
using it as a subquery, that's why I only want one result)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help to concatenate a result...
Thanks for the tip.. Here's the query that worked...
SELECT GROUP_CONCAT(DISTINCT segfees SEPARATOR ',')
from segfees
WHERE ID_flights_segments=[insert value here]
group by ID_flights_segments;
inferno wrote:
Hi,
You can use select group_concat(segfees) from fsf;
The information bellow is from mysql manual, but you have to have mysql
4.1.x
=
*
|GROUP_CONCAT(/|expr|/)|
This function returns a string result with the concatenated
non-|NULL| values from a group. It returns |NULL| if there are no
non-|NULL| values. The full syntax is as follows:
GROUP_CONCAT([DISTINCT] /|expr|/ [,/|expr|/ ...]
[ORDER BY {/|unsigned_integer|/ | /|col_name|/ | /|expr|/}
[ASC | DESC] [,/|col_name|/ ...]]
[SEPARATOR /|str_val|/])
mysql *|SELECT student_name,|*
- *|GROUP_CONCAT(test_score)|*
- *|FROM student|*
- *|GROUP BY student_name;|*
Or:
mysql *|SELECT student_name,|*
- *|GROUP_CONCAT(DISTINCT test_score|*
- *|ORDER BY test_score DESC SEPARATOR ' ')|*
- *|FROM student|*
- *|GROUP BY student_name;|*
In MySQL, you can get the concatenated values of expression
combinations. You can eliminate duplicate values by using
|DISTINCT|. If you want to sort values in the result, you should
use |ORDER BY| clause. To sort in reverse order, add the |DESC|
(descending) keyword to the name of the column you are sorting by
in the |ORDER BY| clause. The default is ascending order; this may
be specified explicitly using the |ASC| keyword. |SEPARATOR| is
followed by the string value that should be inserted between
values of result. The default is a comma (‘|,|’). You can remove
the separator altogether by specifying |SEPARATOR ''|.
You can set a maximum allowed length with the
|group_concat_max_len| system variable. The syntax to do this at
runtime is as follows, where |val| is an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = val;
If a maximum length has been set, the result is truncated to this
maximum length.
=
Best regards,
Cristi
Cory @ SkyVantage wrote:
What I need to do is take this query and this result:
SELECT segfees FROM fsf;
+-+
| segfees |
+-+
| FS=5.00 |
| AY=2.50 |
| XF=1.75 |
| ZP=3.20 |
+-+
I make it just return ONE row like this:
+-+
| segfees |
+-+
| FS=5.00,AY=2.50,XF=1.75,ZP=3.20 |
+-+
Any idea how to write a query to return this type of result? (I plan on
using it as a subquery, that's why I only want one result)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
