Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


The Many Uses of Coalesce in SQL Server

By:   |   Read Comments (41)   |   Related Tips: 1 | 2 | More > Functions - System

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Many times people come across the Coalesce function and think that it is just a more powerful form of ISNULL. In actuality, I have found it to be one of the most useful functions with the least documentation.  In this tip, I will show you the basic use of Coalesce and also some features you probably never new existed.


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

For example,


will return the current date.  It bypasses the first NULL values and returns the first non-null value.

Using Coalesce to Pivot

If you run the following statement against the AdventureWorks database

FROM HumanResources.Department 
WHERE (GroupName = 'Executive General and Administration') 

you will come up with a standard result set such as this.

human resources

If you want to pivot the data you could run the following command.

DECLARE @DepartmentName VARCHAR(1000) 

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

SELECT @DepartmentName AS DepartmentNames 

and get the following result set.

department names

Using Coalesce to Execute Multiple SQL Statements

Once you can pivot data using the coalesce statement, it is now possible to run multiple SQL statements by pivoting the data and using a semicolon to separate the operations. Let's say you want to find the values for any column in the Person schema that has the column name "Name". If you execute the following script it will give you just that.


   (Clmn VARCHAR(500),  
    Val VARCHAR(50)) 

+ COLUMN_NAME + ''' AS Clmn, Name FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + 
   AND xtype = 'U'  
   AND TABLE_SCHEMA = 'Person' 



here is the result set.

virgin islands

My personal favorite is being able to kill all the transactions in a database using three lines of code. If you have ever tried to restore a database and could not obtain exclusive access, you know how useful this can be.


SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '  
FROM sys.sysprocesses  
WHERE DBID=DB_ID('AdventureWorks') 

PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute 

will give you a result set such as the following.

Next Steps
  • Whenever I think I may need a cursor, I always try to find a solution using Coalesce first.
  • I am sure I just scratched the surface on the many ways this function can be used. Go try and see what all you can come up with. A little innovative thinking can save several lines of code.

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Monday, January 09, 2017 - 4:38:50 AM - Ramesh Back To Top

i have some problem in colacse funcation but i want group by in this proce Please Solve My Problem






WHERE 1=1 AND [email protected] GROUP BY HLSNO 




Wednesday, September 09, 2015 - 6:53:13 AM - Ash Back To Top

My favourite use of COALESCE is not really mentioned, so thought i'd put my 2p in.

Say you have a query with where clause with lots of antijoins, something like this:

SELECT Count(1) --how many A's are missing corresponding B, C and D?

I tend to simplify this with:


similarly if there is a single NOT NULL condition:

AND A.somecol IS NOT NULL;

I like to use:

WHERE COALESCE(B.ID, C.ID, D.ID, A.col) = A.somecol;


Disclaimer Even when used correctly, the the above doesn't seem to make a notable improvement in performance. My intentions using this are normally to cut code bloat and make it more readable. If, however where there are many different datatypes used in the comparissons, implicit conversion is done before the check is made. This actually degrades performance compared to seperate conditions where there are numerous data types in play, and either multiple COALESCES with data-type grouped sets of values can be used or indivdual comparisons can be used instead.


Monday, November 10, 2014 - 9:25:57 AM - Chloe Back To Top

Hi Alex.

Because Ramesh's original question was why doesn't

give 2 as the answer. Darek tried to explain that the COALESCE is sensitive to data type precedence. Before the coalesce function 
is run, it checks what data types it is comparing. i.e. INT, INT, INT and DATETIME. It decides that for this function they should all be 
processed as DATETIME and implicitly converts them before it calls the funtion. So 2 becomes 1900-01-03 00:00:00.000 (as 
demonstrated by 
10 becomes 1900-01-11 00:00:00.000 and 5 becomes 1900-01-06 00:00:00.000. 
Note that 0 always equals 1900-01-01 00:00:00.000 when representing datetime values as integers.
Coalesce then selects the first non-null value and returns 1900-01-03 00:00:00.000.
There is an inherent danger in using mixed data types in the same function but had the original question 
constructed the SELECT statement in either of the following three ways, the answer of 2 would have been returned.
SELECT CAST(COALESCE(null,2,10,5,getdate()) AS INT)
Finally, try playing around with the values set in the following code and see the different results
 @i2 INT,
 @i3 INT,
 @i4 INT,
SET @i1 = NULL
SET @i1 =3
SET @i3 = NULL
SET @i4 = NULL
IF COALESCE(@i1, @i2, @i3, @i4, @d5) < ISNULL(@d5, '2099-12-31')
SELECT CAST( COALESCE(@i1, @i2, @i3, @i4, @d5) AS INT)
SELECT COALESCE(@i1, @i2, @i3, @i4, @d5)

Saturday, October 18, 2014 - 2:56:18 PM - Alex Back To Top

Hi, I'm new to this discussion, and have been using SQL Server for a while, now but just getting more involved in development.

Can you please discuss Ramesh's question, I think Darek tried to answer it but I'm not sure it's clear.

I think Ramesh expects for 2 to return, instead of a date, right?

When I run either of the code I get something in the lines of 1900-01-03 00:00:00.000, really confused...

so this:

SELECT COALESCE(null,2, 10,5,getdate())


1900-01-03 00:00:00.000

and this:

SELECT COALESCE(null,2,10,5,getdate()),CAST(2 AS DATETIME);


(No column name) (No column name)
1900-01-03 00:00:00.000 1900-01-03 00:00:00.000

Please explain, thanks





Tuesday, June 10, 2014 - 9:22:36 PM - Reza Hossain Back To Top

Thanks for this post, very useful.

Friday, November 01, 2013 - 3:33:50 PM - Darek Back To Top


Here's an answer to your question - run this:


COALESCE is sensitive about data type precedence. It treats 2 as DATETIME, it casts it into DATETIME before outputting.




Wednesday, October 02, 2013 - 10:22:25 AM - Ramesh Back To Top


According to your first point coalesce returns the first non-null expression among its arguments. but when i execute the below query then it not showing the first non-null expression.

SELECTCOALESCE(null,2, 10,5,getdate()) 

output shows the date but as per function it shoul be 2. 

Please advise me why it is not showing first non null expression.

Friday, April 26, 2013 - 7:42:51 PM - Arts Back To Top

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

Thursday, February 14, 2013 - 2:21:26 AM - Shiv Back To Top

These tips helped a lot.. Thanks!!

Friday, June 08, 2012 - 10:35:21 PM - Candy Back To Top
I use COALESCE quite a lot in my applications, and I would rnmocmeed everybody else to as well. It's a quick function, that would become quite messy if left up to your PHP/ASP applications to sort.

Tuesday, May 22, 2012 - 10:23:53 AM - Faith Sloan Back To Top

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

Tuesday, May 01, 2012 - 7:53:40 AM - raj Back To Top


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



[email protected]

[email protected]


empty(which means nothing)

[email protected]


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

Sunday, July 04, 2010 - 2:02:28 PM - troulouth Back To Top
Hey I'm new. Brilliant forum. Just found it on Google. Thank u 4 the Brilliant community we have here :)

Thursday, July 16, 2009 - 2:55:15 PM - toniupstny Back To Top


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.


****************************   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]

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

@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

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


SELECT  tst_Product.productID,  tst_Product.styleNum, tst_Product.description
    , dbo.gettags(tst_Product.productID)
FROM  tst_Product
 where productID in (select productID from tst_ProductTag)

Thursday, July 16, 2009 - 4:58:48 AM - toniupstny Back To Top


 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)
ALTER TABLE [dbo].[tst_ProductTag] DROP CONSTRAINT [tst_ProductTag_tagID]
ALTER TABLE [dbo].[tst_ProductTag] DROP CONSTRAINT [tst_ProductTag_productID]
DROP Table tst_ProductTag

IF Exists (Select productID from tst_Product)
  Drop Table tst_Product

IF Exists (Select tagID from tst_Tag)
  Drop Table tst_Tag


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.
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'

@DelimitedString = COALESCE(@DelimitedString+', ' , '') + tst_Tag.tagID
FROM tst_ProductTag
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.



Thursday, July 16, 2009 - 12:19:14 AM - xmetaldetectorx Back To Top

 Consider these tables



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


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

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'

@DelimitedString = COALESCE(@DelimitedString+', ' , '') + tblTags.tag
FROM tblProductTag
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..

Monday, November 03, 2008 - 7:09:07 AM - aprato Back To Top

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

Sunday, November 02, 2008 - 10:08:24 PM - mohhosny Back To Top

 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.

Sunday, November 02, 2008 - 2:38:41 PM - toniupstny Back To Top

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.



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

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

Sunday, November 02, 2008 - 12:54:19 PM - mohhosny Back To Top


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)


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

Friday, July 04, 2008 - 2:42:55 AM - moot67 Back To Top

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


Thursday, July 03, 2008 - 1:53:49 PM - moonreader Back To Top

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

Thursday, July 03, 2008 - 1:46:51 PM - toniupstny Back To Top
Ken, I thought the article was fine and brought up some discussion which makes people think (almost always a good thing).  You are braver than most to even write an article.


Thursday, July 03, 2008 - 1:06:45 PM - KenSimmons Back To Top

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.


Thursday, July 03, 2008 - 10:32:10 AM - TimothyAWiseman Back To Top

 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.

Thursday, July 03, 2008 - 9:10:32 AM - moot67 Back To Top

[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.


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.

Thursday, July 03, 2008 - 8:04:06 AM - toniupstny Back To Top

[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.

memberid initialjob

123          GS
124       PDR


memberid intermediatejob
123         AAP
125        AV

memberid lastjob
123        TS
126         CS

and you need to have all these tables into TABLEFINAL:

memberid initialjob  intermediatejob lastjob
123        GS          AAP                TS
124       PDR          NULL               NULL
125       NULL         AV                  NULL
126       NULL         NULL              CS


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

DECLARE @t1 TABLE (memberid INT,initialjob VARCHAR(50))
DECLARE @t2 TABLE (memberid INT,intermediatejob VARCHAR(50))
DECLARE @t3 TABLE (memberid INT,lastjob VARCHAR(50))




set statistics io on
set statistics time on

select coalesce(t1.memberid,t2.memberid,t3.memberid) 'memberid', initialjob, intermediatejob, lastjob
from @t1 t1
full outer join @t2 t2 on t1.memberid=t2.memberid
full outer join @t3 t3 on t3.memberid=t2.memberid
--order by 1     (If you so desire)



Thursday, July 03, 2008 - 7:39:22 AM - aprato Back To Top

= 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


Thursday, July 03, 2008 - 7:25:53 AM - tosscrosby Back To Top

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?

Thursday, July 03, 2008 - 5:59:21 AM - moonreader Back To Top

nice! thanks

Thursday, July 03, 2008 - 5:46:41 AM - moot67 Back To Top

[quote user="damar123"]

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



 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

            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

you end up with this table def...

          [col0] [tinyint]             NOT NULL,
          [col1] [tinyint]             NULL,
          [col2] [int]                 
          [col3] [numeric](13, 0)  NULL,
          [col4] [numeric](24, 0)  NULL

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

Thursday, July 03, 2008 - 4:53:54 AM - PeggySue Back To Top

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!

Thursday, July 03, 2008 - 4:22:20 AM - JimR Back To Top

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.)

Thursday, July 03, 2008 - 3:52:43 AM - damar123 Back To Top

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

Consider the example:

DECLARE @tinyint tinyint

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

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

So, be carefull when using those two statements.


Thursday, July 03, 2008 - 2:51:58 AM - moot67 Back To Top

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

SET @DepartmentName ''

SELECT @DepartmentName @DepartmentName + Name ';' 
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 ?

Thursday, July 03, 2008 - 1:37:01 AM - TheCodeForce Back To Top

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.

set nocount on

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 :)


Wednesday, July 02, 2008 - 6:41:25 PM - KenSimmons Back To Top

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

Wednesday, July 02, 2008 - 1:05:23 PM - PeggySue Back To Top

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?

Wednesday, July 02, 2008 - 11:15:32 AM - PeggySue Back To Top

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!

Wednesday, June 18, 2008 - 2:15:39 PM - KenSimmons Back To Top

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.

Wednesday, June 18, 2008 - 12:09:23 PM - moonreader Back To Top

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:

DECLARE @T table (col1 varchar(100))

INSERT @T select 'one';

INSERT @T select 'two';

INSERT @T select 'three';

INSERT @T select 'four';

DECLARE @Data varchar(1000)

SET @Data=''

SELECT @data = @data + col1 + ','  FROM @T


Learn more about SQL Server tools