/*sql server 2008 CTE split and join fun*/

/*split begins*/

if object_id(N'tempdb..#split') is not null drop table #split;

if object_id(N'tempdb..#joined') is not null drop table #joined;

declare @fun varchar(64) = 'The quick brown fox jumped over the lazy dogs!';

declare @delim char(1) = ' ';

select @fun as [Fun];

with split(i, token, remainder) as

(select 1

, left(@fun,charindex(@delim,@fun)-1)

, LTRIM(right(@fun,len(@fun)-CHARINDEX(@delim,@fun)))

union all

select i + 1

,case when charindex(@delim,remainder) > 0 then

left(remainder,charindex(@delim,remainder)-1)

else remainder end as token

,LTRIM(right(remainder,len(remainder)-CHARINDEX(' ',remainder))) as remainder

from split

where charindex(@delim,remainder) >= 0 and token != remainder

)

select i, token, remainder

into #split

from split;

select * from #split;

/*join begins*/

with joined (i, newfun, token) as (

select i, convert(varchar(max),token), token

from #split where i = 1

union all

select s.i, j.newfun + @delim + s.token, s.token

from joined j

inner join

#split s

on s.i = j.i + 1

)

select *

into #joined

from joined;

select * from #joined;