Monday, August 10, 2009

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'

No comments: