Wednesday, January 31, 2007

[sql] Using Coalesce to Glue Row Results Together

Found this trick today by accident! What a find. I have been wanting to know how to do this without using cursor for a very, very long time.

declare @UserList varchar(100);
select @UserList = coalesce(@UserList+',', '') + UserID from account where UserID like 'Bob%';
select @UserList;

This would return the comma delimited list of UserIDs, that all start with Bob.