You can do this using nested CURSORs.  You'd need to create a temporary 
table and iterate each doctor using a cursor.  For each itteration of the 
doctors cursor you could then get a cursor of the specialities for each 
doctor, and iterate that to build a concatenated string of the results.  
Both values - the doctor & their specialities could then be inserted into 
the temp table.  At the end of iterating the cursor of doctors you can 
then just SELECT from the temporary table.  You'll need to test this 
though as I don't know how fast it will be - depends on the number of 
doctors that you're reading.

Regards,
Mark.


-----Original Message-----
From: musicand@h...
Sent: 01 January 1601 00:00
To: musicand@h...; sql_language@p...
Subject: [sql_language] How Do a SubQuery That Concatenates All Found
Values Into One Row?


Date:   12/04/2000  11:26 pm +0000  (Monday) 
From:  musicand@h...
To:  "sql language" <sql_language@p...>
Subject:  [sql_language] How Do a SubQuery That Concatenates All Found
          Values Into One Row?

How Do I do A SubQuery So that all the rows returned are put into a
concatenated string (deliminated by a comma), and put into one column of
one row?

Example:
   Have Table:
      Doctors
      ------------
      DoctorName      DoctorSpecialty

A Doctor Can have multiple specialties, and I want to return the doctor's
name once, with all their specialties as one string:
     Joe Doe         Endocrinologist,Optomologist,Other

How Do I do This?