I have seeen some examples of what I am trying to do using COALESCE and FOR XML (seems like the better solution). I just can't quite get the syntax right.

Here is what I have (I will shorten the fields to only the key ones):

Table                    Fields
------                   -------------------------------
Requisition              ID, Number
IssuedPO                 ID, Number
Job                      ID, Number
Job_Activity             ID, JobID (fkey)
RequisitionItems         ID, RequisitionID(fkey), IssuedPOID(fkey), Job_ActivityID (fkey)

I need a query that will list ONE Requisition per line with its associated Jobs and IssuedPOs. (The requisition number start with "R-" and the Job Number start with "J-").

Example:

R-123 | "PO1; PO2; PO3" | "J-12345; J-6780"

Sure thing Adam!

Here is a query that returns multiple rows. I have to use outer joins, since not all Requisitions have RequisitionItems that are assigned to Jobs and/or IssuedPOs (in that case their fkey IDs would just be null of course).

SELECT DISTINCT Requisition.Number,  IssuedPO.Number, Job.Number
        FROM Requisition
        INNER JOIN RequisitionItem on RequisitionItem.RequisitionID = Requisition.ID
        LEFT OUTER JOIN Job_Activity on RequisitionItem.JobActivityID = Job_Activity.ID
        LEFT OUTER JOIN Job on Job_Activity.JobID = Job.ID
        LEFT OUTER JOIN IssuedPO on RequisitionItem.IssuedPOID = IssuedPO.ID