How to concatenate records

From: Shirley <schee_at_rpisun1.mda.uth.tmc.edu>
Date: 1995/10/11
Message-ID: <45h7a2$c6h_at_oac4.hsc.uth.tmc.edu>#1/1

RDBMS: 7.2
OS: Unix Platform
Question: How do I concatenate records?
I have 2 tables, Location and Diagnosis. Location table has 3 fields
Patient_ID
Date
Location
where Patient_ID, Date and Location form the Primary Index

Diagnosis table has 3 fields
Patient_Id
Date
Diagnosis
where Patient_Id, Date and Diagnosis form the Primary Index

A patient may have a disease at several Locations (and vice versa). For example:
Location Table

---

Patient_id = 123
Date = May 10, 1995
Location = distal end of femur

Patient_id = 123
Date = May 10, 1995
Location = shaft of femur

Patient_id = 123
Date = May 10, 1995
Location = prox. end of femur

Diagnosis Table

---

Patient_id = 123
Date = May 10, 1995
Diagnosis = Osteosarcoma

When I query the above tables (join by patient id), I would like the output in the format shown below:

Patient_id    Location                                                   
                                 Diagnosis


-----------------------------------------------------------------------


----------

123    distal end of femur, shaft end of femur, prox. end of femur       

 Osteosarcoma

instead of the cartesian product:
123 distal end of femur Osteosarcoma

123    shaft end of femur     Osteosarcoma
123    prox. end of femur      Osteosarcoma

Thanks is advance. Received on Wed Oct 11 1995 - 00:00:00 CET