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?