mayooran mayooran - 3 months ago 5
SQL Question

How can I print multiple outcomes of the same records in a single row?

I have the below table which is ordered by the column "CaseID".

enter image description here

I need to print the output in the below displayed manner where all the outcomes have been grouped together for each cases using commas. I have written the below query to get everything else but not sure how to append all the outcomes.

SELECT c.CaseID AS 'Case #',
c.EventDate AS 'Date',
CONCAT(d.DrugName+'-',m.ManufacturerName) AS 'Drug & Manufacturer',
CONCAT(CONVERT(VARCHAR(10),c.Age)+' ',a.AgeUnitName) AS 'Age',
c.Sex,
ou.OutcomeName
FROM Consumes con
INNER JOIN [Case] c
ON con.FKCaseID = c.CaseID
INNER JOIN Drug d
ON d.DrugID = FKDrugID
INNER JOIN Manufacturer m
ON m.ManufacturerID = con.FKManufacturerID
INNER JOIN AgeUnit a
ON a.AgeUnitID = c.FKAgeUnitID
INNER JOIN Case_Outcome oc
ON oc.FKCaseID = c.CaseID
INNER JOIN OutCome ou
ON oc.FKOutcomeID = ou.OutcomeID
GROUP BY c.CaseID,c.EventDate,d.DrugName,m.ManufacturerName,c.Age,a.AgeUnitName,c.Sex,ou.OutcomeName


enter image description here

How can I achieve this? Please advice.

Answer

Put all your query in CTE, then use FOR XML:

;WITH cte AS (
--your query goes here
)

SELECT c.[Case #],
       c.[Date],
       c.[Drug & Manufacturer],
       c.[Age],
       c.[Sex],
       STUFF((SELECT ','+OutcomeName
       FROM cte
       WHERE c.[Case #] = [Case #]
       FOR XML PATH('')),1,1,'') as OutcomeName
FROM cte c
GROUP BY c.[Case #],
       c.[Date],
       c.[Drug & Manufacturer],
       c.[Age],
       c.[Sex]