In the past concatenating string values across rows in T-SQL is the kind of thing that typically involved recursive UDFs, temp tables, or some such. Another option was introduced with CTEs back in SQL 2005 which can be recursive.
For an example I'm essentially going to combine a table of words into sentences. Consider this structure:
declare @words table ( ID int identity, Word_Text varchar(1024), Sentence int )
With this data:
insert into @words select 'i', 1 insert into @words select 'am', 1 insert into @words select 'concatenated', 1 insert into @words select 'you', 2 insert into @words select 'are', 2 insert into @words select 'not', 2
So the idea is that we should get two resultant sentences, "i am concatenated" and "you are not".
So POW, here it is:
;with CTE_Sentence (Num, Word_Text, Sentence) as ( select distinct cast(1 as int), cast('' as varchar(max)), Sentence from @words union all select cast(T.Num + 1 as int), CTE.Word_Text + ' ' + T.Word_Text, T.Sentence from CTE_Sentence CTE inner join ( select row_number() over ( partition by Sentence order by ID ) AS Num, Word_Text, Sentence from @words ) T on T.Num = CTE.Num and T.Sentence = CTE.Sentence ) select Sentence, ( select top 1 Word_Text from CTE_Sentence CTE where CTE.Sentence = W.Sentence order by Num desc ) Words from @words W group by Sentence
Which results in :
Sentence Words 1 i am concatenated 2 you are not
Mon Jan 01 2007 00:00:00 GMT+0000 (UTC)