Taking a colums multiple rows and turning it into a single row. 1

[chunkII123]

Well, here it is - I feel that I am proficient in MySQL syntax, but something must be evading my memory, or I'm just simply retarded. I am attempting to take a column, in this case it's called "Hobby:", which produces multiple results, with two other columns with repeats - those being "Name:" and "Member Number:" consecutively.

Here is the syntax I'm currently using:

SELECT CONCAT(m.FirstName,' ', m.LastName) AS 'Name:', m.MemberNumber AS 'Member Number:', ph.Name AS 'Hobby:', h.HobbyID AS 'Hobby ID:'
FROM (membersinformation m JOIN hobbies h)
JOIN possiblehobbies ph
ON h.hobbyid = ph.ID AND m.membernumber = h.membernumber
ORDER BY m.membernumber;

And here is just an example of what it produces (| indicates new column)

Name: | Member Number: | Hobby: | Hobby ID
John Doe | 999 | Cars | 1
John Doe | 999 | Paintball | 53
John Doe | 999 | 4x4 | 95

etc...

What I would like to accomplish here is something like this:

Name: | Member Number: | Hobby: | Hobby ID
John Doe | 999 | Cars, Paintball, 4x4 | 1, 53, 95

As you can see, what I am looking to do, is exchange each "extra" row out, so that each member would appear only one in the list, and list their hobbies as CSV's. I thought this might be possible, but I need a hand from a much more proficient database expert, or I could just be mental; whichever, doesn't matter. Essentially this is so I can take this into my Java application, and insert it into a jTextfield, because I can't use a table in this case.

Thank you ahead of time every/ anyone that helps.

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[TrojanWarBlade]

Not my area of expertise but you might want to look at "group_concat".


Trojan.

 

[chunkII123]

See I knew there was something I was missing, thanks much Trojan!

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'