COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value. This tip describes two creative uses of the COALESCE() function in SQL Server.

this functionis very much usefull in building a comma seprated strings which i used in my project

i used this functions to get all the id's based on a common value

declare @oid varchar(8000)

SELECT @oid = COALESCE(@oid +',', '') +convert(varchar(1000),ConsumerOrderids ) 
from ConsumerOrderInfowhere consumerid = 124

select @orderdetailsid

the output was
1001,1002,1003