SQL Stuff Function With Multiple Joins

Hello Experts! I'm having trouble with a SQL (2005) query. It's a large query with multiple joins, but to avoid massive row duplication I'm currently having to leave data out. Here is the current query:
SELECT     Occur_Types.Occur_Type AS Type, Master.ODT AS [Date Occurred], Master.ODT AS [Time Occurred], Master.SDT AS [Date Submitted], 
                      Master.SDT AS [Time Submitted], Master.InvName AS [Name of Involved], Contact_Types.Name_Type AS [Involved Type], Master.DOB AS [Involved DOB], 
                      Grievance.PatRoom AS Room, Grievance.ConSpecific AS [Other Information], Grievance.ConPhone AS [Contact Phone], Master.Description, 
                      Master.CompBy AS [Submitted By], Departments.Department, (CASE DeptReview.Location WHEN 1 THEN 'Yes' ELSE 'No' END) AS Location, 
                      DeptReview.Findings AS [Department Findings], Users.Full_Name AS [Department Review By], DeptReview.R_Date AS [Dept Review Date], 
                      Grieve_Admin_Review.Findings AS [Review Findings], Grieve_Admin_Review.Doc AS [Physician Involved], 
                      (CASE Grieve_Admin_Review.Letter WHEN 1 THEN 'Yes' ELSE 'No' END) AS [Letter Sent], Users_2.Full_Name AS [Last Saved By], 
                      Users_1.Full_Name AS [Signed By], Grieve_Admin_Review.RevDate AS [Date Signed or Saved]
FROM         Forms INNER JOIN
                      Master ON Forms.FID = Master.FID INNER JOIN
                      Grievance ON Master.OID = Grievance.OID AND Master.OID = Grievance.OID INNER JOIN
                      Contact_Types ON Master.NTID = Contact_Types.NTID INNER JOIN
                      Occur_Types ON Master.TID = Occur_Types.TID INNER JOIN
                      DeptReview ON Master.OID = DeptReview.OID INNER JOIN
                      Departments ON DeptReview.DeptID = Departments.DeptID INNER JOIN
                      Users ON DeptReview.SigID = Users.UID INNER JOIN
                      Grieve_Admin_Review ON Grievance.OID = Grieve_Admin_Review.AID LEFT OUTER JOIN
                      Users AS Users_2 ON Grieve_Admin_Review.SaveBy = Users_2.UID LEFT OUTER JOIN
                      Users AS Users_1 ON Grieve_Admin_Review.Sig = Users_1.UID
WHERE     (Master.ODT > CONVERT(DATETIME, '10/01/2014', 101))

Open in new window

I have another table called 'GrieveConcerns' which stores individual concern entries such as Billing, Medication, or Physician Error and links to the Master.OID (occurrence ID). It is quite common to have multiple concerns to one occurrence, so I would like to be able to concatenate those entries into a single column to prevent duplicating rows.

Instead of:
Example of multiple rowsdisplay this:
Example of concatenated column
I feel the STUFF function may do what I need, but I can't wrap my head around how to make it work in this large query. Any assistance appreciated.
LVL 3
JHMH IT StaffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Try adding this to your select list (double check my schema to make sure it matches)

STUFF(
	SELECT ', ' + Concern
	FROM GrieveConcerns
	WHERE OID = Master.OID
	ORDER BY [Date]
	FOR XML PATH('')
), 1, 2, '') AS ConcernList

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JHMH IT StaffAuthor Commented:
Now I have another monkey wrench to throw in it Brian. 'GrieveConcerns' is a relational table and only holds a small amount of data. The actual text I need in the field is contained in a table called Occur_Sub_Types. The join looks like this:
SELECT     Occur_Sub_Types.Sub_Type
FROM         GrieveConcerns INNER JOIN
                      Occur_Sub_Types ON GrieveConcerns.CID = Occur_Sub_Types.SID

Open in new window

I need the Sub_Type from that join to STUFF into 'ConcernList'. Is that as simple as adding the join to the code you posted?
JHMH IT StaffAuthor Commented:
WOOHOO!
JHMH IT StaffAuthor Commented:
Brian, final code:
SELECT     Occur_Types.Occur_Type AS Type, Master.ODT AS [Date Occurred], Master.ODT AS [Time Occurred], Master.SDT AS [Date Submitted], 
                      Master.SDT AS [Time Submitted],
                      Master.InvName AS [Name of Involved], Contact_Types.Name_Type AS [Involved Type],
                      STUFF((SELECT     ',  ' + Occur_Sub_Types.Sub_Type
							FROM         GrieveConcerns INNER JOIN
									Occur_Sub_Types ON GrieveConcerns.CID = Occur_Sub_Types.SID
									WHERE GrieveConcerns.OID = Master.OID
							ORDER BY Master.OID
							FOR XML PATH('')), 1, 2, '') AS [Concern List],							 
                      (CASE WHEN Grievance.PatRoom IS NULL THEN 'n/a' ELSE Grievance.PatRoom END) AS Room, 
                      (CASE WHEN Grievance.ConSpecific IS NULL THEN 'n/a' ELSE Grievance.ConSpecific END) AS [Other Information], 
                      (CASE WHEN Grievance.ConPhone IS NULL THEN 'n/a' ELSE Grievance.ConPhone END) AS [Contact Phone], 
                      Master.Description, 
                      Master.CompBy AS [Submitted By], Departments.Department, (CASE DeptReview.Location WHEN 1 THEN 'Yes' ELSE 'No' END) AS Location, 
                      DeptReview.Findings AS [Department Findings], Users.Full_Name AS [Department Review By], DeptReview.R_Date AS [Dept Review Date], 
                      (CASE WHEN Grieve_Admin_Review.Findings IS NULL THEN '* pending' ELSE Grieve_Admin_Review.Findings END) AS [Review Findings], 
                      (CASE Grieve_Admin_Review.Letter WHEN 1 THEN 'Yes' ELSE 'No' END) AS [Letter Sent], 
                      (CASE WHEN Users_2.Full_Name IS NULL THEN '' ELSE Users_2.Full_Name END) AS [Last Saved By], 
                      (CASE WHEN Users_1.Full_Name IS NULL THEN '* pending' ELSE Users_1.Full_Name END) AS [Signed By], 
                      Grieve_Admin_Review.RevDate AS [Date Signed or Saved]
FROM         Forms INNER JOIN
                      Master ON Forms.FID = Master.FID INNER JOIN
                      Grievance ON Master.OID = Grievance.OID AND Master.OID = Grievance.OID INNER JOIN
                      Contact_Types ON Master.NTID = Contact_Types.NTID INNER JOIN
                      Occur_Types ON Master.TID = Occur_Types.TID INNER JOIN
                      DeptReview ON Master.OID = DeptReview.OID INNER JOIN
                      Departments ON DeptReview.DeptID = Departments.DeptID INNER JOIN
                      Users ON DeptReview.SigID = Users.UID INNER JOIN
                      Grieve_Admin_Review ON Grievance.OID = Grieve_Admin_Review.AID LEFT OUTER JOIN
                      Users AS Users_2 ON Grieve_Admin_Review.SaveBy = Users_2.UID LEFT OUTER JOIN
                      Users AS Users_1 ON Grieve_Admin_Review.Sig = Users_1.UID
WHERE     (Master.ODT >= CONVERT(DATETIME, '01/01/2015', 101))

Open in new window

It's doing EXACTLY what I needed - thanks so much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.