MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1521

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!

damar123:

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

<snip>

 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