Query to combine the table (with subquery or so) to reduce data redundant

Question

Before what is the query to make the data in [DiagnosisName] table became one like (name1 , name2) so the [PatientName] table didn't show redundant.

Result I Want

Please include what you've tried so far. Also, putting your input and desired results directly in the question instead of as images is appreciated. — Stephen Newell, Mar 27 '18 at 03:22
I'm sorry im new to this forum This is my query to show [Diagnosis Name] --There is query to select registrationNo, etc-- (SELECT d.DiagnosisName + ',' FROM Diagnosis dd WHERE dd.DiagnosisName = d.DiagnosisName GROUP BY dd.DiagnosisName FOR XML PATH('')) [Diagnosis Name] From Registration r --Inner join stuff etc- INNER JOIN EpisodeDiagnosis ed ON ed.RegistrationNo = r.RegistrationNo INNER JOIN Diagnosis d ON d.DiagnosisCode = ed.DiagnosisCode GROUP BY d.DiagnosisName — user9555813, Mar 27 '18 at 03:27
Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — RoMEoMusTDiE, Mar 27 '18 at 03:30

PSK · Answer 1 · 2018-03-27T04:15:22.790

You can try like following.

SELECT *,
STUFF((SELECT ',' + DiagnosisName 
            FROM   [Table_Name] T1 
            WHERE  T1.RegistrationNo = T2.RegistrationNo 
            FOR XML PATH('')), 1, 1, '')     
FROM
(
   SELECT DISTINCT 
       RegistrationNo, 
       MedicalNo, 
       PatientName, 
       Gender, 
       Umur, 
       [Bed No], 
       [Payer Type]         
   FROM   [Table_Name] 
) T2

Query to combine the table (with subquery or so) to reduce data redundant

1 Answers1