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?

• Sabin B: For table Z : the second column have saved also the character "A" - double quotes ? or just simple value like A
• Magier: this is just sample data - simple varchar, the quotes just indcate this is some text.

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"

• Magier: PERFECT SOLUTION!

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

• Magier: This does not work as needed. It delivers separate Rows for each row for each RZ entry instead of concatenation of the Z.Name column values for each unique R item. So it's result is similar to a simple join.