Show only the top n entries and add '...' FOR XML

Question

I want to list only a few records (say 3) and adds ... if there is more in one line using FOR XML PATH ('').

So far i have written

;WITH SRC AS (
    select 'A' grp, 'abc' rec
    union select 'A', 'def'
    union select 'A', 'ghi'
    union select 'A', 'jkl'
    union select 'B', 'mno'
)
SELECT (
SELECT STUFF((
SELECT TOP 3 ',' + rec FROM SRC
WHERE SRC.grp = tableA.grp
FOR XML PATH('')
), 1, 1, '') + CASE WHEN (SELECT COUNT(1) FROM SRC WHERE SRC.grp = tableA.grp) > 3 THEN ',...' ELSE '' END
)
FROM (SELECT 'A' grp) tableA

The above works, but I want to know if there is a way not to select from the SRC twice for the job (one for data, one for counting), because where clause can be great in some cases.

I cannot move the where clause to the CTE because this condition depends on the result of another choice (e.g. select from tableA).

Using MS SQL SERVER 2008 R2

thank

Answer 1

XML, 4 . ... .

with SRC as 
(
    select 'A' grp, 'abc' rec
    union select 'A', 'def'
    union select 'A', 'ghi'
    union select 'A', 'jkl'
    union select 'B', 'mno'
    union select 'B', 'pqr'
)
select (
       select ','+S2.X.value('.', 'nvarchar(max)')
       from (
            select top(4) S.rec, '...' as eli
            from SRC as S
            where S.grp = tableA.grp
            for xml path('X'), type
            -- order by ?
            ) as S1(X)
         cross apply S1.X.nodes('(X[position() lt 4]/rec, X[position() eq 4]/eli)/text()') as S2(X)
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'nvarchar(max)')
from (select 'A') as tableA(grp);

?

XML, .

select top(4) S.rec, '...' as eli
from SRC as S
where S.grp = tableA.grp
for xml path('X'), type
-- order by ?

<X>
  <rec>abc</rec>
  <eli>...</eli>
</X>
<X>
  <rec>def</rec>
  <eli>...</eli>
</X>
<X>
  <rec>ghi</rec>
  <eli>...</eli>
</X>
<X>
  <rec>jkl</rec>
  <eli>...</eli>
</X>

XML nodes()

S1.X.nodes('(X[position() lt 4]/rec, X[position() eq 4]/eli)/text()') as S2(X)

X[position() lt 4]/rec rec X, X[position() eq 4]/eli eli .

XML .

abc
def
ghi
...

XML for xml path , substring(text()[1], 2) values(), .

:

row_number() , , a. , order by row_number(), , , .

with SRC as 
(
    select 'A' grp, 'abc' rec
    union select 'A', 'def'
    union select 'A', 'ghi'
    union select 'A', 'jkl'
    union select 'B', 'mno'
    union select 'B', 'pqr'
)
select (
       select top(4) ','+case when row_number() over(order by S.rec) = 4 then '...' else S.rec end
       from SRC as S
       where S.grp = tableA.grp
       order by S.rec
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'nvarchar(max)')
from (select 'A') as tableA(grp);