0

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?

  • For table Z : the second column have saved also the character "A" - double quotes ? or just simple value like A – Sabin Bio Dec 6 '16 at 18:36
  • this is just sample data - simple varchar, the quotes just indcate this is some text. – Magier Dec 7 '16 at 8:29
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"
  • PERFECT SOLUTION! – Magier Dec 7 '16 at 8:43
0

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
  • 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. – Magier Dec 7 '16 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.