Hi, Could anyone tell me how to write the query to combine all the values of a column together if they have the same value in another column (similar to group by operation).
e.g:
Say I have a table (name_subject) with two columns: Name, subject
The two columns together form the primary key.
A sample data in the table will sth. like this:

Name   Subject
Jackie Maths
Jackie Physics
Jackie Chemistry
Mary   Maths
Mary   Physics
Mary   Chemistry

Now I want generate table like this:

Name   AllSubjects
Jackie Maths,Physics,Chemistry
Mary   Maths,Physics,Chemistry

How shall I write the sql query?
Thanks a lot.

Roger

You can get all the records for Mary followed by all the records for Jackie (etc) using
      SELECT * FROM subjects GROUP BY Name

Personally, I would then combine the subjects within my client as I step through the rows returned one by one, and output the results in a format under the control of my program in PHP, Perl or whatever.

I notice that you say "I want generate table" ... with the output. You really should not write a new table containing the combined data, as it goes against the principles of database design - as soon as you start duplicating the same data in two different formats, you have a maintenance nighmare as you try to keep the two in step.

Thanks for you reply, Graham.  
Sorry for my vague expression.   What I mean is not to physically create another table in the database, but to create a temporary result set ( in a table form) for the application.
I agree that your suggestion is one possible alternative.
But I hope, if possible, the result set could be generated at the database level, rather than at application level. (so that I don't have to go through each row to do some operations in the application)
Do you have any idea how to write the SQL query to generate the result set ?

Regards,

Roger

MySQL does have a CONCAT function to join strings together, but it's going to be more difficult with what you're wanting to do to select a whole series of values from one column where the values in another column are equal to actually feed into the CONCAT.  I'm not an Oracle DBA / Programmer, but I believe that this is the sort of thing you would do under the PL section of PL/SQL and not under the SQL which is really all MySQL has (yes, there's good stuff adding in at release 4 but the production release is still 3.23.53 and I would stick with that for production systems   )

You've given a very simple example of a table to manipulate - one of the reasons that I'm so reluctant to push MySQL to try to achieve what you want is there's a question in the back of my mind  ... "and what are we going to do with other fields on the lines that we connect?".    Using PL,  I think you have the tools to do whatever with other fields;  with Perl, PHP, Java, etc you have the tools too.   Going to the limits within the SQL of MySQL, doing data manipulation with a lean database engine, I think you might find yourself being scuppered as you extend the code.

All this "IMHO" (in my humble opinion);  please do post if you manage to achieve the task in pure (My)SQL - you might persuade me that you have a good solution but at present I think it's "horses for courses" and the horse for your manipulation should be  a programming language and not a database access language.

Thank you for your advice,Graham
I will post the solution if I can do it using MySQL query.

---