I have the below query which returns a dataset such as this:
1 2 2 10 2012-03-26 14:12:10.000
1 2 2 40 2012-03-26 14:12:10.000
1 2 2 60 2012-03-26 14:12:10.000
This is because the m table has one row that matches @clientmedicationid, and the b table contains three rows that relate to the m table record foreign key. This is correct.
I want to instead return one row representing the m table, and show the b table 10,40 and 60 values as a comma delimited list in one column of that row. There could be up to 9 possible values/rows in b table for each unique row in m.
SELECT m.new_currentmedication, m.new_takingasindicated, m.new_doctordiscontinued, b.new_reasonfornoncompliance, m.createdon
FROM new_medicationcompliance m WITH (NOLOCK)
JOIN new_medcompliancereasonformednoncompliance b WITH (NOLOCK)
ON b.new_medicationcompliancereviewid = m.new_medicationcomplianceid
WHERE m.new_clientmedicationid = @clientmedicationid
AND m.deletionstatecode <> 2
AND m.statecode = 0