how to concatenate text from multiable rows in SSRS?

Question

I got introduced to SSRS recently and I am facing a problem in extracting data from the database. I searched online and I found out that this is called concatenate text from multiable rows. Now, I tried a code.. but in the results, it give me the data duplicated next to each other separated by a comma. Now I am happy that it is separating the result with a coma... but why giving me the data duplicated?

The Code:

select distinct wm1.Date, p1.[Medical Record Number], Wound_Type =CONCAT
((
select distinct  CAST(wt2.Name as VARCHAR(MAX))+ ' /// ' from dbo.[Wound Type] as wt2
inner join dbo.[Wound Management] as wm2 on wm2.[Wound Type_ID] = wt2.ID
inner join dbo.Patient as p2 on wm2.[Owner (Patient)_Patient_ID]=p2.ID
where wm1.Date=wm2.Date
FOR XML PATH('')
),wt1.Name)
from dbo.[Wound Type] as wt1
inner join dbo.[Wound Management] as wm1 on wm1.[Wound Type_ID] = wt1.ID
inner join dbo.Patient as p1 on wm1.[Owner (Patient)_Patient_ID]=p1.ID
group by wm1.Date, wt1.Name, p1.[Medical Record Number]

the result:

Please Help.

Is it because your CONCATing `,wt1.Name` with the wt2 names? — Hannover Fist, Aug 27 '21 at 22:17

score 0 · Accepted Answer · answered Aug 27 '21 at 22:53

Your data is duplicated because you are CONCATing the list of wt2.Names with the wt1.Name - ,wt1.Name). The one with NULL date isn't finding a matching record and is only showing the wt1.Name

Actually I think you may want to use the STUFF function instead of CONCAT to remove any extra slashes at the end.

SELECT DISTINCT wm1.Date, p1.[Medical Record Number], Wound_Type = STUFF( 
    (
    SELECT DISTINCT ' /// ' + CAST(wt2.Name as VARCHAR(MAX)) 
    from dbo.[Wound Type] as wt2
    inner join dbo.[Wound Management] as wm2 on wm2.[Wound Type_ID] = wt2.ID
    inner join dbo.Patient as p2 on wm2.[Owner (Patient)_Patient_ID]=p2.ID
    where wm1.Date=wm2.Date
    FOR XML PATH('')
    ), 1, 5, '' )
from dbo.[Wound Type] as wt1
inner join dbo.[Wound Management] as wm1 on wm1.[Wound Type_ID] = wt1.ID
inner join dbo.Patient as p1 on wm1.[Owner (Patient)_Patient_ID]=p1.ID
group by wm1.Date, wt1.Name, p1.[Medical Record Number]

This doesn't fix that NULL date issue. You could wrap the STUFF in an ISNULL.

SELECT DISTINCT wm1.Date, p1.[Medical Record Number], Wound_Type = ISNULL(STUFF( 
    (
    SELECT DISTINCT ' /// ' + CAST(wt2.Name as VARCHAR(MAX)) 
    from dbo.[Wound Type] as wt2
    inner join dbo.[Wound Management] as wm2 on wm2.[Wound Type_ID] = wt2.ID
    inner join dbo.Patient as p2 on wm2.[Owner (Patient)_Patient_ID]=p2.ID
    where wm1.Date=wm2.Date
    FOR XML PATH('')
    ), 1, 5, '' )
    , wt1.Name)
from dbo.[Wound Type] as wt1
inner join dbo.[Wound Management] as wm1 on wm1.[Wound Type_ID] = wt1.ID
inner join dbo.Patient as p1 on wm1.[Owner (Patient)_Patient_ID]=p1.ID
group by wm1.Date, wt1.Name, p1.[Medical Record Number]

Here's an explanation of stuff:

How Stuff and 'For Xml Path' work in SQL Server?

how to concatenate text from multiable rows in SSRS?

1 Answers1