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)
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
after successfully creating the function, here we are going to see the cool result by following query.
select DBO.Get18QI(‘BC’) as ComaValues
Hope you people will find this article useful.