joining results in single record?

[Ondernemer]

I have a table with 2 records:

==========
TABLE: users
==========

name_id (INT)
name (varchar)
code (int)

Inside the table I have a few records:

id name code
-----------------
1. John 233
2. Fred 211
3. John 122
4. John 555
5. Fred 444

(this is a simplified example of the actual tables so bare with me)

Now I want to select all users and COMBINE the codes and INSERT them into a
new table like this:

=============
TABLE: combined
=============

id (INT)
name (varchar)
combined_codes (varchar)

id name combined_codes
-----------------------------
1. John 233, 122, 55
2. Fred 211, 444

I have tried the CONCAT_WS statement, but code like "CONCAT_WS( ',', code,
code) only combines 2 of the same codes.

Is there a way to do this?

Tnx.

[Paul Bramscher]

Ondernemer wrote:

I have a table with 2 records:

==========
TABLE: users
==========

name_id (INT)
name (varchar)
code (int)

Inside the table I have a few records:

id name code
-----------------
1. John 233
2. Fred 211
3. John 122
4. John 555
5. Fred 444

(this is a simplified example of the actual tables so bare with me)

Now I want to select all users and COMBINE the codes and INSERT them into a
new table like this:

=============
TABLE: combined
=============

id (INT)
name (varchar)
combined_codes (varchar)

id name combined_codes
-----------------------------
1. John 233, 122, 55
2. Fred 211, 444

I have tried the CONCAT_WS statement, but code like "CONCAT_WS( ',', code,
code) only combines 2 of the same codes.

Is there a way to do this?

Tnx.

I'd do it in PHP or some other coding language like this:
(1)create a new table (to handle combined situation).
(2)Issue a select distinct on user name + code in your first table.
Order by username.
(3)using PHP, cycle through the set and collect the data with a simple
algorithm. Use a previous/current variable to flag a change in
username. if no change, keep concatenating the $combined_value variable.
(4) when a change occurs, write out a single line: containing the
(previous, before the change)username and concatenated variable to your
new combined table.

There's probably a way to do it with just SQL, but why fry your brain on
that route? My general rule of thumb is that the more complex the
logic, the more it begs to be done outside of SQL. :-)

-Paul Bramscher