Use Stuff() Function to combine multiple rows
<p>I have read posts on the stuff () function and I believe it is the function I should be using for my query. </p>

<p>However, I need to take the example at this post further and add more columns, and include a sub query that pulls the reason text that matches my reason ID from another table. I have a table with a date, patientID, reason, otherNotes... I need to combine all the notes/reasons for each patient and each date into one column so that:</p>

<pre><code>Date        patientID       reason .....other columns....
__________________________________________________________
12/26/2016   2              age
12/26/2016   2              location
12/27/2016   2              missing info
12/27/2016   2              age
</code></pre>

<p>becomes</p>

<pre><code>Date        patientID       reason .....other columns....
__________________________________________________________
12/26/2016   2              age, location
12/27/2016   2              missing info, age
</code></pre>

<p>Here is what I started to create but it is giving me all the reasons for the patient not just the ones that belong to the date. In addition I don't know how to add to the query to pull the rest of my columns.</p>

<p>Please help me create a good strong query.</p>

<pre><code>     SELECT distinct DATEADD(dd, DATEDIFF(dd, 0, dateAdded), 0), 
            otherNote = STUFF((SELECT ', ' + (CASE WHEN eligibilityReason= 7 
            THEN otherNote ELSE Reasons.reason end) as reason 
            FROM Eligibility left Join Reasons on Reasons.reasonID = eligibilityReason 
            WHERE patientID=2 
                FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
            FROM Eligibility 
            GROUP BY DATEADD(dd, DATEDIFF(dd, 0, dateAdded), 0)
</code></pre>