Recursive Selects

Last week at work I created some SQL Server functions to take a list of rows and turn them into a comma delimited string.  It feels like data formatting which should be done on the client side, but that was the method we went with.

It does show recursive selects though, which can also be used for dynamic cross tab queries.  At first this method didn't make any sense to me, but thinking about a select statement as a loop makes it make sense.

declare @siblings table (id int, firstName varchar(max))

insert into @siblings

      select 0, 'Jordan' UNION

      select 1, 'Mia'

declare @siblingString varchar(max)

set @siblingString = ''

select @siblingString = @siblingString + firstName + ', '

      from @siblings

if (datalength(@siblingString) > 2)

      select @siblingString = left(@siblingString, datalength(@siblingString) -2)

select @siblingString

Outputs 'Jordan, Mia'