CTE Concatenation

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

POW.

Mon Jan 01 2007 00:00:00 GMT+0000 (UTC)