It may be noted that COALESCE is not necessarily what permits some of the tricks you have defined here. You can do some of them without the use of COALESCE. The following code exemplifies the PIVOT technique without COALESE being used:

That's correct.  Actually ISNULL would have worked in all the cases except the first.  I just wanted to raise awareness of the function while showing some tips at the same time.

I was SO happy to come across this function that I didn't know about, and see it work beautifully - that I almost got teary-eyed!  I have worked SO hard to try to display multiple rows in one field!  To find an answer that works this smoothly and cleanly was GREAT!  Thanks!

The query works beautifully as written and on its own.  When I try to use it as part of a HUGE stored procedure, with multiple temp tables and select statements, it doesn't work though.  I know it's because I'm not using it correctly.  Could someone look at it for me and give me some feedback?

Sure.  Post the code that you are having a problem with.

The method shown does indeed work well, but is limited in that it cannot be used as part of bigger queries or in views. You might find the following more to your taste, which does not has this limitation and seems to produce the same queryplans, so performance wise the methods are a match.

declare @t table (col1 varchar(100))

insert @t select 'one';

insert @t select 'two';

insert @t select 'three';

insert @t select 'four';

select substring( (select convert( varchar(max), ',' ) + col1 from @T order by col1 for xml path('') ), 2, 1024000 ) 

Note the convert to a varchar(max) which works only in SQL Server 2005 and later. It's sole purpose is to force a character datatype that can hold practically any size of string. The comma is put before the column to make sure it has a fixed location, that can later be easily removed with the substring function. The result is a clean comma separated list of whatever is stored in col1.

I use this method quite often to produce comma separated lists of numbers by putting a convert to varchar around col1. Looking inside a comma separated character field can then be done with something like (pseudo code):

charindex( ',' + convert( numeric field to match ) + ',', ',' + the comma separated list of IDs + ',' ) > 0

I hope you find this just as useful :)

Cheers!

As mentioned above, ISNULL could have been used for most of the examples, or even just assigning a value to the variable before the select statement ie.

DECLARE @DepartmentName VARCHAR(1000) 

SELECT @DepartmentName = @DepartmentName + Name + ';' 
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

 So... are there any uses for COALESCE other than just as a longer version of ISNULL ?

there is at least ONE BIG difference when using ISNULL and COALESCE:

Consider the example:

SELECT COALESCE (@tinyint, 500) -- this is ok

SELECT ISNULL(@tinyint,500) -- this produces arithmetic overflow error

Excellent tips in both the original article and comments!  (I also thought that the title was misleading, in that coalesce is not critical to doing the neat things done in the examples, but I learned much from them.)

I ended up using turning the example into a function and calling it from my stored procedure.  Worked great!  I will be using this in many variations in the future!  Thanks again to all!

[quote user="damar123"]

there is at least ONE BIG difference when using ISNULL and COALESCE:

<snip>

[/quote]

 Thanks, that's useful to know.

 I expanded your example a bit to test what's going on, and if you run this...

DECLARE @tinyint tinyint

SELECT
            ISNULL(@tinyint, 1) AS col0,
            CONVERT(tinyint,ISNULL(@tinyint, 1)) AS col1,
            COALESCE (@tinyint, 1) AS col2,
            COALESCE (@tinyint, 5000000000000) AS col3,
            COALESCE (@tinyint, 500000000000000000000000) AS col4
INTO TESTTABLE

you end up with this table def...

the differences in field defs and the NULL/NOT NULL is quite interesting. 

nice! thanks

What timeliness! I used coalesce for the first time this week. ISNull didn't seem to catch all the nulls in a particular column (still not sure why??) that I was trying to default to a particular value. It left 44 rows out of 250 as still showing null. I used coalesce(field1, field2, 'A') and it worked every time for every row. Wish I had found this years ago. Anyone else experience crazy results using is null or = null?

= null won't work unless you change the ANSI NULL setting to off (ie set ansi_nulls off)

NULL equates to unknown in the ANSI standard

 If I recall correctly,  6.5 allowed = null to work by default

[quote user="moot67"]([/quote]   - Are there other uses for Coalesce vs replacement for ISNULL

 This is my first post so pardon the formatting (or lack thereof).

ISNULL evaluates one item and does a substitution of like type if the value is null.   Coalesce allows multiple items to be compared in one statement.

Here is a use that might be hard to do with ISNULL, where you are combining data from several tables:

Situation is where you need to combine information based on matches of one column of data in multiple tables.

For this you can use Coaelesce to combine the data even if there is no matching row in one or more of the tables:

 Toni...

[quote user="toniupstny"]

ISNULL evaluates one item and does a substitution of like type if the value is null.   Coalesce allows multiple items to be compared in one statement.

[/quote]

I understand the basic usage but the original article said:

"Let's start with the documented use of coalesce. According to MSDN, coalesce returns the first non-null expression among its arguments."

Which sort of implied that it did something else as well - I just wondered what that might be.

interesting bit here: http://blogs.msdn.com/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx 
particularly about the query execution plans.

 I thought this was an excellent article and passed it on to some of my team members, but one statement you made was that Coalesce is more than just a more powerful version of isnull.  Everything in the article seemedto fit with the idea of a more powerful version of isnull.

Sorry about the controversy about the article.  I see a lot of questions about how to pivot data and I also know a lot of people who have never used the coalesce function.  I was just trying to raise awareness of them both.  I should have explained that better in the article.

Again, sorry if I mislead anyone. That was not my intention.

 Thanks,

I agree. Thanks for starting such a lively discussion, with lots of interesting variations on your original theme.

Yes - definitely food for thought.  The examples of pivoting data have been particularly useful.

Thanks

Helpppp please

 hey i guys i keep searching for the best way to search into table with best performance and easy to write techniqe

i tried the Coalesce and it was perfect for me but i found one thing that drive me back and think again about useing it which is Coalesce with Cloumn with null values. for example...

create a table with clumn ID,Name,Title,FLow

insert (1,MM,Null,BB)

insert(2,AA,CC,Null)

SELECT     Name, Title, Flow
                          
FROM         Table
                          
WHERE     Name = COALESCE (@name, name) AND title = COALESCE (@title, title) AND flow = COALESCE (@flow, flow)

it didn't return anything

because of the null values.

so what is the best thing we can do to avoid this problem..

thanks in advance

Hi Mohhosny. If I follow you correctly you want to be able to show all the rows even if one of the values contains a null?   If so, this would do it:

 Hope this helps.

Toni

********************************************************

drop table tbla
create table tbla ( ID  int,Name varchar(10),Title varchar(10),FLow varchar(10))

insert into tbla
values (1,'MM',Null,'BB')

insert into tbla
values(2,'AA','CC',Null)

SELECT     Name=COALESCE (name, 'NullName'),
        Title=COALESCE (title, 'NullTitle'), Flow = COALESCE (flow, 'Nullflow')
                         
FROM     tbla

 Thanks Toni for your response..

your idea working if only i want to select but i want to search in table

what i need is something like that

declare @name nchar(50)
declare @title nchar(50)
declare @flow nchar(50)

set @name = 'M'
set @title = null
set @flow = null

  SELECT     Name, Title, Flow                      
FROM     tbla
WHERE     Name like COALESCE ('%' + @name + '%', name) AND title = COALESCE (@title, title)
AND flow = COALESCE (@flow, flow) 

so if i pass all parameters with null it returns all rows.and if i set any parameter it returns all the rows contains that parameter even if some other parameter contains null values like the table you have created.

You can CASE

select *
from mytable
where name like case when @name is null then name else @name end
and email = case when @email is null then email else @email end

Note that you'll likely get a table scan

 Consider these tables

Product
productID
styleNumber
description

Tag
tagID
tag

These two tables have many-to-many relationship, so I made another table

ProductTag
productID
tagID

Now I want to get all products and corresponding tags, so the output looks like this. I will make a view of it.

View
productID - styleNum - description - tags
1 - '1111' - 'description text' - 'tag1, tag2, tag3'

2 - '2222' - 'description text' - 'tagX, tagY, tagZ'

Can i use Coalesce to achieve this? 

So far I am able to only return one row with this code:

 declare @DelimitedString varchar(max),
@productID varchar(max)

SET @productID = '51fc1d0d-5476-4378-b6c4-0a2a9ceb1aea'

SELECT
@DelimitedString = COALESCE(@DelimitedString+', ' , '') + tblTags.tag
FROM tblProductTag
LEFT JOIN tblTags
ON tblProductTag.tagID = tblTags.tagID
WHERE tblProductTag.productID = @productID


SELECT tblProducts.productID, tblProducts.styleNum, tblProducts.description, tblProducts.inStock, @DelimitedString as tags
FROM tblProducts
WHERE tblProducts.productID = @productID

But i need to have all products..

 XMetal....

 Maybe your data and/or table definitions need tweeking.   When I set up and tested your method it returned the results you were looking for:

 ******************************************************************************

IF Exists (Select tagID from tst_ProductTag)
   begin
ALTER TABLE [dbo].[tst_ProductTag] DROP CONSTRAINT [tst_ProductTag_tagID]
ALTER TABLE [dbo].[tst_ProductTag] DROP CONSTRAINT [tst_ProductTag_productID]
 
DROP Table tst_ProductTag
end

IF Exists (Select productID from tst_Product)
  Drop Table tst_Product

IF Exists (Select tagID from tst_Tag)
  Drop Table tst_Tag

GO

create table tst_Product
 (productID varchar(4) Constraint tst_Product_productID primary key
  , styleNum  int, Description varchar(20))

create table tst_Tag
 (tagID varchar(4) Constraint tst_Tag_tagID primary key, tag int)

create table tst_ProductTag
 (tagID varchar(4) Constraint tst_ProductTag_tagID Foreign Key References tst_Tag(tagID)
 ,productID varchar(4)
 Constraint tst_ProductTag_productID Foreign Key (productID) References tst_Product(productID)  
 , Constraint tst_ProductTag_PK Primary key (productID,tagID))

Insert into tst_Tag
  Select 'tag1', 1
    Union all Select 'tag2', 2
    Union all Select 'tag3',3
    Union all Select 'tagx', 4

Insert into tst_Product
  Select 'prd1', 1 , 'Product 1'
    Union all Select 'prd2', 2 , 'Product 2'
    Union all Select 'prd3',3 , 'Product 3'
    Union all Select 'prdx', 4 , 'Product 4'

Insert into tst_ProductTag (productID, tagID)
  Select 'prd1','tag1'
    Union all Select 'prd1','tag2'
    Union all Select 'prd1','tag3'
    Union all Select 'prd2','tag2'
    Union all Select 'prd2','tag3'

/*
Now I want to get all products and corresponding tags, so the output looks like this. I will make a view of it.
View
productID - styleNum - description - tags
1 - '1111' - 'description text' - 'tag1, tag2, tag3'

2 - '2222' - 'description text' - 'tagX, tagY, tagZ'

*/
 declare @DelimitedString varchar(20),
@productID varchar(20)

SET @productID = 'prd1'

SELECT
@DelimitedString = COALESCE(@DelimitedString+', ' , '') + tst_Tag.tagID
FROM tst_ProductTag
LEFT
JOIN tst_Tag
ON tst_ProductTag.tagID = tst_Tag.tagID
WHERE tst_ProductTag.productID = @productID

Select 'String is ' + @DelimitedSTring


SELECT tst_Product.productID, tst_Product.styleNum, tst_Product.description
    , @DelimitedString as tags
FROM  tst_Product
WHERE tst_Product.productID = @productID

*********************************************************************

 Which gives the result:

******************************

------------------------------
String is tag1, tag2, tag3

(1 row(s) affected)

productID styleNum    description          tags                 
--------- ----------- -------------------- --------------------
prd1      1           Product 1            tag1, tag2, tag3

(1 row(s) affected)

************************************

By the way, I don't think you don't need to use a LEFT Join if there are Foreign Key Constraints ensuring that a tagID in the ProductTag tbl must

have a corresponding tagID in the Tag tbl.  Also you should combine the queries to yield the results.  (I might play with that a bit and post an update later).

 Please let me know if you get different results.

Toni

XM...

With the same data/tables as in my prior post, you could build a function to determine the tags then invoke it for each productID.  There is probably another way to do it too but this is what I have.

Toni

****************************   Create the Function  ***

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gettags]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[gettags]
GO

create function gettags (@productid varchar(4))
returns varchar(2000)
as
begin
 declare @DelimitedString varchar(20)

SELECT
@DelimitedString = COALESCE(@DelimitedString+', ' , '') + tst_Tag.tagID
FROM tst_ProductTag
JOIN tst_Tag
    ON tst_ProductTag.tagID = tst_Tag.tagID
 
 WHERE tst_ProductTag.productID = @productID

return @DelimitedString
end

*********************    Now Invoke it for each product in the ProductTag table ***

hi all,

is it possible to retrieve the date which has 'null' and an empty data in a column ...

for ex:

i had the table cust_table with cid, cname and cmail

and in cmail column i had the data like

cmail

------

adds@gmail.com

adad@ymail.co

null

empty(which means nothing)

ada@redif.com

now i need the data which has to display the null and empty rows

i had one solution:

select * from cust_table where cmail = '' or cmail is null

by execution it retrives two records....

i need another solution..

THANK YOU, Ken for getting this discussion started. You caused me to think outside the box re: some queries I am writing. Thank the community for your valuable insight. I LOVE THE 'NET!

Faith Sloan

These tips helped a lot.. Thanks!!

O My God. THANKS! Thanks for explaining so clearly.