Grouping multiple rows by a common field

[DeanHughes]

Hi there,

I am currently writing some php to show some info from one of our databases and i have come to a brick wall.

I have an Oracle database with two tables, one table holds personal details (name, address etc) and the other holds skills that are associated to the people in table one. I have created the query below but im not getting the deisred results.

What im getting is :
|person|skills|
| 1 | A |
| 1 | B |
| 1 | C |
| 1 | D |
| 2 | A |
| 2 | D |
| 2 | E |
| 3 | A |
| 3 | C |
| 4 | B |

What i want is :
|person| skills |
| 1 |A, B, C, D|
| 2 |A, D, E |
| 3 |A, C |
| 4 |B |

I have the following code :

$day = date("d");
$month = date("F");
$year = date("Y");

$conn=odbc_connect('zcmr','###','###');
if (!$conn)
  {exit("Connection Failed: " . $conn);}

$sql="SELECT * FROM (mnme a left join ccndmat b on a.nm_idno = b.cm_rf_nm) join cattbs c on b.cm_at_at = c.at_cd where a.nm_type in ('C') and nm_rupd='$day-$month-$year' order by nm_idno";

$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}

echo "<table border='0'><tr><td><b>$day $month $year</b></td></tr>";
while (odbc_fetch_row($rs))
{
  $fname=odbc_result($rs,"nm_fname");
  $lname=odbc_result($rs,"nm_lname");
  $skill=odbc_result($rs, "at_des");
  echo "<tr><td>$fname $lname</td><td>$skill</td></tr>";
}
odbc_close($conn);
echo "</table>";

Which allows me to select all the data that i need, but im stuck as to what to use to get the skill codes apended to one field, anyone got any pointers of the kind of thing i need to be looking at to achieve this?

Many Thanks!

Dean

[Zyppora]

I think what you’re looking for is the GROUP BY clause? I’m pretty sure Oracle supports that.

If I’m wrong here, you might also try solving this on the PHP level, rather than in the SQL. Takes some coding skills, but is easier to wrap your mind around (IMHO).

[DeanHughes]

Thanks, i did try the GROUP BY command but i was getting errors that i couldnt fix so i drew up the conclusion that Oracle didnt like it.

What would be the method for doing it in PHP? any tips on the kind of commands i would use would be cool so i can go take a look.

[Zyppora]

You could track an array of strings, in which the indexes are the user IDs and the values are concatenated skills. Just loop through the results and add the skills to the respective array value.