T-SQL Query, combine columns from multiple rows into one column

Question

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

Here's what I have (I will reduce the fields to key only):

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 in a row with the associated Job and IssuedPOs. (The application number starts with "R-" and the job number starts with "J-").

Example:

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

Of course, Adam!

Here is a query that returns multiple rows. I have to use external joins, since not all details have RequisitionItems that are assigned to Jobs and / or IssuedPOs (in this case their fkey identifiers will be just null).

 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 

Answer 1

Here is one way to do this using subqueries:

 select 'R-' + cast(r.number as varchar(32)) as RequisitionNumber , ( select 'PO' + CAST(ip.number as varchar(32)) + ';' from IssuedPO ip join RequisitionItems ri on ip.id = ri.IssuedPOID where ri.RequisitionID = r.id for xml path('') ) as POList , ( select 'J-' + CAST(j.number as varchar(32)) + ';' from Job j join Job_Activity ja on j.id = ja.JobID join RequisitionItems ri on ri.Job_ActivityID = ja.id where ri.RequisitionID = r.id for xml path('') ) as JobList from Requisition r