Concat related data (text) from multiple related rows into single field to omit repeating data

Question

I'm trying to do pretty much the same thing as these questions:

How to concatenate text from multiple rows into a single text string in SQL server?

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

I believe I'm working in SQL Server 2005.

But I need to either, A.) Do it with multiple tables, selecting about 12 columns and using roughly 8 inner joins

or

B.) Do it with a Query that already exists with the multiple tables (using roughly 8 inner joins)

Here is the current Query:

SELECT        Claim.ClaimID, ClaimStatus.ClaimStatus, Claim.Claimant, Claim.ClaimNumber, ClaimType.ClaimType, Facility.FacilityName, StateOrProvince.State, 
                        MedicalPractitioner.FullName, Claim.ClaimOccurrenceDate, Patient.Allegation, Injury.InjuryDescription AS Injury, GetClaimTotals_vw.[Reserve Total], 
                        ISNULL(GetClaimTotals_vw.[Expense Total], '$0') AS [Paid Total]
FROM            StateOrProvince INNER JOIN
                        MedicalPractitioner INNER JOIN
                        Injury INNER JOIN
                        Patient ON Injury.InjuryID = Patient.Injury INNER JOIN
                        ClaimStatus INNER JOIN
                        ClaimType INNER JOIN
                        Facility INNER JOIN
                        Claim ON Facility.FacilityID = Claim.Facility INNER JOIN
                        GetClaimTotals_vw ON Claim.ClaimID = GetClaimTotals_vw.ClaimID ON ClaimType.ClaimTypeID = Claim.ClaimType ON ClaimStatus.ClaimStatusID = Claim.ClaimStatus ON 
                        Patient.PatientID = Claim.Patient AND Claim.ClaimID = Patient.ClaimNumber INNER JOIN
                        ClaimInsureds ON Claim.ClaimID = ClaimInsureds.ClaimNumber ON MedicalPractitioner.PractitionerID = ClaimInsureds.MedicalPractitioner ON 
                        StateOrProvince.StateOrProvinceID = Facility.StateProvince

ORDER BY ClaimStatus.ClaimStatus

Query I tried, but it just puts every single name for each Case/Claim and still repeats Cases/Claims

SELECT  ClaimNumber, Stuff(
  (SELECT N', ' + FullName FROM QueryName_1 FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
FROM QueryName_1

Result:

Claim# | Status   | MP(s)       

A1002.     Open.       Alex Amond, John Doe, Richard Cool, Jane Smith

A1002.     Open.       Alex Amond, John Doe, Richard Cool, Jane Smith

A1006.     Pending.    Alex Amond, John Doe, Richard Cool, Jane Smith

A1008.     Closed.    Alex Amond, John Doe, Richard Cool, Jane Smith

Etc.

The result is like 100-200 rows of data.

Specifically, I need to Concat on average two names that are related by an ID in another Table.

Here is an example (I'll use Case# and Lawyers) snapshot of what is currently being output with the current Query:

Claim# | Status   | MP(s)       | Etc. |  |....|

A1002.     Open.       John Doe

A1002.     Open.       Alex Almond

A1006.     Pending.  Richard Cool

A1008.     Closed.    Jane Smith

Etc.

What I need:

Claim# | Status    | MP(s).                           | Etc. |. | ... | 

A1002.     Open.       John Doe, Alex Almond

A1006.     Pending.  Richard Cool

A1008.     Closed.    Jane Smith

Etc.

Thanks.

Please provide the query that you have tried. – Rima Apr 12 '19 at 16:07 — Rima, Apr 12 '19 at 16:07

Rima · Accepted Answer · 2019-04-13T08:10:53.947

Modified query

 stuff((
SELECT 

                   ', ' +     MedicalPractitioner.FullName 
FROM            StateOrProvince INNER JOIN
                        MedicalPractitioner INNER JOIN
                        Patient INNER JOIN
                        CS CS INNER JOIN
                        ClaimType INNER JOIN
                        Facility INNER JOIN
                        Claim C ON Facility.FacilityID = c.Facility 
                        INNER JOIN   GetClaimTotals_vw ON c.ClaimID = GetClaimTotals_vw.ClaimID ON ClaimType.ClaimTypeID = c.ClaimType ON CS.ClaimStatusID = c.ClaimStatus ON Patient.PatientID = c.Patient AND 
                        c.ClaimID = Patient.ClaimNumber INNER JOIN
                        ClaimInsureds ON c.ClaimID = ClaimInsureds.ClaimNumber ON MedicalPractitioner.PractitionerID = ClaimInsureds.MedicalPractitioner ON StateOrProvince.StateOrProvinceID = Facility.StateProvince
where  c.ClaimID =  Claim.ClaimID 
group by C.ClaimID, CS.ClaimStatus
for xml path('')),1,1,'') full name

Try using stuff and xml path

SELECT 
Claim.ClaimID, ClaimStatus.ClaimStatus, Claim.Claimant, Claim.ClaimNumber AS [Claim Number], ClaimType.ClaimType AS [Claim Type], Facility.FacilityName AS [Loss Location], StateOrProvince.State, 
                        MedicalPractitioner.FullName AS Insureds, Claim.ClaimOccurrenceDate AS [Occurrence Date], Patient.Allegation, Patient.Injury, GetClaimTotals_vw.[Reserve Total], ISNULL(GetClaimTotals_vw.[Expense Total], '$0') 
                        AS [Paid Total],

 stuff((
SELECT 

                   ', ' +     MedicalPractitioner.FullName 
FROM            StateOrProvince INNER JOIN
                        MedicalPractitioner INNER JOIN
                        Patient INNER JOIN
                        CS CS INNER JOIN
                        ClaimType INNER JOIN
                        Facility INNER JOIN
                        Claim C ON Facility.FacilityID = c.Facility 
                        INNER JOIN   GetClaimTotals_vw ON c.ClaimID = GetClaimTotals_vw.ClaimID ON ClaimType.ClaimTypeID = c.ClaimType ON CS.ClaimStatusID = c.ClaimStatus ON Patient.PatientID = c.Patient AND 
                        c.ClaimID = Patient.ClaimNumber INNER JOIN
                        ClaimInsureds ON c.ClaimID = ClaimInsureds.ClaimNumber ON MedicalPractitioner.PractitionerID = ClaimInsureds.MedicalPractitioner ON StateOrProvince.StateOrProvinceID = Facility.StateProvince
where  c.ClaimID =  Claim.ClaimID 
group by C.ClaimID, CS.ClaimStatus
for xml path('')),1,1,'') full name




FROM            StateOrProvince INNER JOIN
                        MedicalPractitioner INNER JOIN
                        Patient INNER JOIN
                        ClaimStatus INNER JOIN
                        ClaimType INNER JOIN
                        Facility INNER JOIN
                        Claim ON Facility.FacilityID = Claim.Facility 
                        INNER JOIN   GetClaimTotals_vw ON Claim.ClaimID = GetClaimTotals_vw.ClaimID ON ClaimType.ClaimTypeID = Claim.ClaimType ON ClaimStatus.ClaimStatusID = Claim.ClaimStatus ON Patient.PatientID = Claim.Patient AND 
                        Claim.ClaimID = Patient.ClaimNumber INNER JOIN
                        ClaimInsureds ON Claim.ClaimID = ClaimInsureds.ClaimNumber ON MedicalPractitioner.PractitionerID = ClaimInsureds.MedicalPractitioner ON StateOrProvince.StateOrProvinceID = Facility.StateProvince
ORDER BY ClaimStatus.ClaimStatus

I received this error: "Msg 164, Level 15, State 1, Line 20 Each GROUP BY expression must contain at least one column that is not an outer reference." — PTK, Apr 12 '19 at 18:17
I commented out the group by statement "--group by Claim.ClaimID, ClaimStatus.ClaimStatus" And I seemed to get the results I expected with the concat under the new FullName column! But am still getting repeating data overall. — PTK, Apr 12 '19 at 18:38
you must add group by clause otherwise it will give repeated data. it is giving error because alias for Claim table is C . I have modified the query, you can try that. — Rima, Apr 13 '19 at 08:09
I apologize for the delayed response. I am now receiving this error: "Column 'MedicalPractitioner.FullName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Adding it to the Group By eliminates the error (obviously), but I still receive the repeating data (possibly obviously). Thanks! — PTK, Apr 16 '19 at 14:33
I realized I had forgotten a table as well, I tried to add it in to the Query, but I am not sure if it is correct? Table: Injury, Field: InjuryDescription & InjuryID — PTK, Apr 16 '19 at 19:54

Concat related data (text) from multiple related rows into single field to omit repeating data

1 Answers1