0

I have such problem. My data is like:

Numers(Id) Letters(Id, NumberId)

If I do Join query, I get output like that:

SELECT N.Id, L.Id
FROM Numbers N
JOIN Letters L ON N.Id = L.NumberId

And the output is

1 | a
1 | b
2 | a
2 | b
2 | c

But I would like to have all letters in one column, such as

SELECT N.Id, Letters.Value
FROM Numbers N
CROSS APPLY (SELECT
             (SELECT ',' + L.Id
              FROM Letters L
              WHERE L.NumberId = N.Id
              FOR XML PATH(''))) AS Letters(Value)

And the desired result is

1 | ,a,b
2 | ,a,b,c

Is there any way to make the second output to be a result of an Indexed-View? I have tried with CROSS APPLY and subqueries, GROUP BY and DISTINCT. Maybe I could split this query into simpler queries? Any suggestions?

0 Answers0