Query with join and concatenation of one joined table's column

Question

I have the following structure:

Table R     Table RZ (n-m)    Table Z
-------     -----------------  ---------
R1          R1 | Z1           Z1 | "A"
R2          R1 | Z2           Z2 | "B"
R3          R2 | Z1           Z3 | "C"
R4          R2 | Z3           Z4 | "D"
...         R3 | Z1             ... 
...         R3 | Z2             ... 
...         R3 | Z4             ... 

(I left out some additional complications and further joins to simplify)

I need to query all rows from "R" with some of the columns or "R" including a column in the result that has the concatenated "Name" column of table "Z".

Here is a sample following the given table contents above:

ID of R | Name of Z
--------------------
R1      | "A,B"
R2      | "A,C"
R3      | "A,B,D"

So far a scalar function was in place, used by every row of R, re-querying the R- RZ-Z join again, concatenating and returning the names, but I figured this is very slow.

I tried FOR XML, but I can't manage to combine the XML result delivering the string I want with the rest of the columns required from table R.

What is the best way to solve this?

Answer 1

Try with CROSS APPLY , like this:

SELECT
    R.idR AS [idR]
    ,'"'+ REPLACE(STUFF(CA.NameOfZ,1,1,''),'"','') + '"' AS NameOfZ
FROM
    @tableR AS R
    CROSS APPLY
    (  SELECT
            ','+Z.colX
        FROM 
            @tableZ AS Z
            INNER JOIN @tableRZ AS RZ
            ON  Z.colZ = RZ.colZ
        WHERE
            R.idR = RZ.idR
        FOR XML PATH('')
    )CA(NameOfZ)
WHERE
    CA.NameOfZ IS NOT NULL  

Output:

idR  NameOfZ
R1   "A,B"
R2   "A,C"
R3   "A,B,C,D"

Answer 2

Something like this should work:

select R.ID,
  (STUFF((SELECT CAST(', ' + Z.ID AS VARCHAR(MAX)) 
     FROM Z
     WHERE Z.ID = RZ.ZID
     FOR XML PATH ('')), 1, 2, ''))  
from R  
inner join RZ on R.ID = RZ.RID