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 are going 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

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

  1. Simonn Says:

    Keep working ,great job!

  2. Doug Says:

    Fine, but isn’t there a way to duplicate Oracle’s ref cursor functionality whereby one could just create a UDF called join and then use it like this?

    SELECT join(‘select top 3 col from anytable’, ‘*’) AS COL_A
    FROM SOMETABLE…

    and the result would be:
    COL_A
    —–
    val1*val2*val3

  3. Christopher F Says:

    hi i need query for this situation

    Vehicle name | Driver
    ================
    Car | 2
    Car | 2,3
    Car | 2,5,8
    Car |
    Scooter | 4
    Scooter |
    Scooter 4,6

    I need query without using Cursor

    Vehicle name | Driver
    ================
    Car | 2.3.5.8
    Scooter | 4

  4. Quick Facts Says:

    You you should change the page subject title COALESCE() function in SQL-Server for getting comma seperated value Ritesh’s Blog for SQL-SERVER & .NET to something more catching for your content you write. I loved the the writing all the same.

  5. neil d Says:

    Coalesce doesn’t seem to be the important thing here; you can change

    COALESCE(@QIList + ‘, ‘, ”)

    for

    IsNull(@QIList + ‘, ‘, ”)

    and it works the same. The coalesce simply deals with the inital state of @QIList being null. If you set it to an empty string, (and there are no null values in the table), you can just write @QIList:

    select @QIList = ”
    SELECT @QIList = @QIList + ‘, ‘ + convert(varchar,T.[18QI]) FROM Testing T WHERE T.name = @ID and T.[18QI] is not null

    Useful for concatenation nonetheless.

  6. email templates Says:

    email templates…

    […]COALESCE() function in SQL-Server for getting comma seperated value « Ritesh’s Blog for SQL-SERVER & .NET[…]…


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: