Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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.

share|improve this question
up vote 3 down vote accepted

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 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)'));


This is the text

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

  (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('')));
share|improve this answer
Can you explain what FOR XML PATH, TYPE).value('.[1]','nvarchar(max)'),1,1,''); does and it's purpose? – user11512 Oct 14 '13 at 14:35
FOR XML PATH is described lightly here and here. Essentially, it's a way to trick XML into performing string concatenation for us, but in a defined way (hence the ORDER BY). The TYPE).value stuff is just protection against strings like 'hey<man' from messing up the XML formatting, < is also a special XML character. – Aaron Bertrand Oct 14 '13 at 14:39

Try this

declare @tTable TABLE(ID INT identity, tText nvarchar(100));
declare @tString nvarchar(4000);

insert into @tTable VALUES

select @tString = COALESCE(@tString+' ','') + tText
from @tTable
where id%2 = 1

select @tString
share|improve this answer
This approach isn't guaranteed to concatenate them in the correct order. Or to work at all in fact – Martin Smith Oct 14 '13 at 14:11
This worked perfectly and is much cleaner then the post I had linked to thanks. – user11512 Oct 14 '13 at 14:15
@MatthewVerstraete Please see the many quotes from Microsoft I compiled on the previous link. Even without ORDER BY, we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution and the behavior as I said is plan dependent. – Martin Smith Oct 14 '13 at 14:16
@Matthew it may look cleaner but it is not guaranteed to work. – Aaron Bertrand Oct 14 '13 at 14:18
Ok in light of all the comments I have unmarked the answer and will now be testing @AaronBertrand post – user11512 Oct 14 '13 at 14:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.