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!!!!

Advertisements

2 Responses to “COALESCE() function in SQL-Server for getting comma seperated value”

  1. Ritesh Shah Says:

    Do comment about your experience about his article. It will help me to cater you better.

  2. Ritesh Shah Says:

    Do comment about your experience about his article. It will help me to cater you better.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: