COALESCE() function in SQL-Server for getting comma seperated value

Microsoft Definition:
This function returns first nonnull expresion among its argument.

You can use this function to get column values in one record set with separator like comma, dash etc. Before invention of this function, people used to achieve this kind of stuff by cursor which is time consuming. So, lets have a look at magical function by Microsoft.

Create following table in your database.
create table Testing( mid int ,name varchar(10) ,[18QI] varchar(10))

After creating the table, insert following records.
insert into Testing VALUES(1,'BC','01')
insert into Testing VALUES(2,'BC','10')
insert into Testing VALUES(3,'BC','02')
insert into Testing VALUES(4,'BC','04')

Now, we are all set to see the new powerful function COALESCE() in SQL-Server. Too see the magic, create following function in your database.

CREATE FUNCTION DBO.Get18QI(@ID char(100))RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @QIList varchar(1000)
SELECT @QIList = COALESCE(@QIList + ', ', '') + convert(varchar,T.[18QI]) FROM Testing T WHERE T.name = @ID and T.[18QI] is not null
RETURN @QIList
END

after successfully creating the function, here we go to see the cool result by following query.
select DBO.Get18QI('BC') as ComaValues

Hope you people will find this article useful.
Happy Working!!!!