asked on

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:

display this:

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.

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JHMH IT Staff

ASKER

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 Staff

ASKER

WOOHOO!

JHMH IT Staff

ASKER

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!