Pull every other row, starting at 0, and concatenate them together I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other row starting at 0 (so 0, 2, 4, 6, etc.) and then concatenate the values of Text together. I found https://stackoverflow.com/questions/4799816/return-row-of-every-nth-record which looks to be how to get every other row but I am not sure how to do the concatenation.

Example Table:

Row ID Text
0   0  This
1   1  Do
2   2  is
3   3  not
4   4  the
5   5  use
6   6  text
7   7  text


The result I would want from that table is This is the text.
DECLARE @x TABLE(ID INT, [Text] NVARCHAR(32));

INSERT @x VALUES (0,N'This'), (1,N'Do'),  (2,N'is'),   (3,N'not'), 
                 (4,N'the'),  (5,N'use'), (6,N'text'), (7,N'text');

SELECT LTRIM((SELECT ' ' + [Text] FROM 
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH, TYPE).value('.[1]','nvarchar(max)'));


Result:

This is the text


If you know your strings can't contain characters like &lt;, &gt; and &amp; then it should be slightly more efficient to leave out the TYPE).value bit:

SELECT LTRIM((SELECT ' ' + [Text] FROM 
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH('')));