TSQL: Some interesting usages of Coalesce

The coalesce function is a really powerful function that can help us in various scenarios. It is described in this MSDN document so I am not going to discuss its working in this post. Instead, I will only present a few interesting usages.

  • Calculating overrideable attributes
  • Combining results from Full Outer Join
  • String concatenation


Calculating overrideable attributes
Perhaps the best usage of coalesce function is when we need to pick up some value that can be declared at various levels, and each level can have different priorities. E.g. in one of our scenarios, the rate of a resource can be assigned on the task he/she worked, or on the project the task belongs to, or on the department the project belongs to, or on the resource itself. Here’s a quick query for such situation:

;With TimesheetWithAllRates as
(
  Select
    ResourceID
    ,HoursWorked
    ,RateFromTask = ( Select Top(1) Rate from TaskRate tr where tr.TaskID = ts.TaskID and tr.ResourceID = ts.ResourceID )
    ,RateFromProject = ( Select Top(1) Rate from ProjectRate pr where pr.ProjectID = ts.ProjectID and pr.ResourceID = ts.ResourceID )
    ,RateFromDept = ( Select Top(1) Rate from DepartmentRate dr where dr.DeptID = ts.DeptID and dr.ResourceID = ts.ResourceID )
    ,RateFromResource = ( Select Top(1) Rate from Resource r where r.ResourceID = ts.ResourceID )
    ...
  From Timesheet ts
)
,TimesheetWithApplicableRate as
(
  Select ResourceID
    ,HoursWorked
    ,Rate = Coalesce(RateFromTask, RateFromProject, RateFromDept, RateFromResource) --starting from left, pickup the first non-null value
    ...
  From TimesheetWithAllRates
)

Notice that we build all the possible rate combinations in the first CTE and then picked the appropriate one according to desired priority.

Combining results from Full Outer Join
Another interesting scenario for the usage of coalesce is when we need to combine values from two result sets. Suppose we have two tables/CTEs: ProjectAmountsUsingMethod1 and ProjectAmountsUsingMethod2 having similar columns and we want to see the row-by-row comparison of both the tables per Project. Here’s a result set assumption:

ProjectAmountsUsingMethod1  as
(
   Select ProjectID, Sum(Amount), ...
   From ... (method1)
)
,ProjectAmountsUsingMethod2 as
(
  Select ProjectID, Sum(Amount), ...
  From ... (method2)
)

Assume that both methods do not yield same result and we need to compare them. Also, there can exists some Project rows using method1 that are missing when we use method2 and vice verca. So we may need to join both the result set using a full outer join. Here’s how the coalesce function can build a better output set for comparison (although for this very example, we can also use IsNull since there are only two parameters but this concept can be extended further as well):

Select coalesce(m1.ProjectID, m2.ProjectID), m1.Amount as M1Amount, m2.Amount as M2Amount
From ProjectAmountsUsingMethod1 m1
Full Outer Join ProjectAmountsUsingMethod2 m2 on m1.ProjectID = m2.ProjectID

String concatenation
This is a fairly common scenario described by various people. If we need to build a comma separated list of values from a certain column into a variable, then one method is to use coalesce(or IsNull) as:

Declare @myVariable varchar(max) 

Select @myVariable = coalesce(@myVariable + ',', '') + MyColumn
From MyTable

That’s all. I hope this post helped you in determining how and when to use the coalesce function. Please share your thoughts by leaving a comment in case you have experienced any other interesting usage of coalesce.

Advertisements

5 Responses to “TSQL: Some interesting usages of Coalesce”

  1. DotNetShoutout Says:

    TSQL: Some interesting usages of Coalesce « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. elameen Says:

    Thank you

  3. online HOme Career Says:

    I do not know whether it’s just me or if everyone else experiencing problems with your blog. It appears as if some of the text on your content are running off the screen. Can someone else please provide feedback and let me know if this is happening to them as well? This may be a problem with my web browser because I’ve had this
    happen before. Thanks

  4. http://www.Articlesnatch.com/profile/Mitch-Schwarz/2147130 Says:

    Wheen I was worried about my own children getting a heat
    rash, I put corn starch in the craks of thwir skin.
    Wear solid-colored cheap scrubs with fanc tie-dyed nursing clogs, or
    put on your cutest Tasmanian Devil cheap scrubs with your solid white nursing clogs.
    If you haave mastitis, the best thing you can do is to climb into
    bed with your baby and stay there.

  5. search engine Says:

    You may get excellent discounts using the internet once you store at the conclusion of any period.

    When you shop on the web, you need to make sure that your website is
    a reliable vendor. Successful Buying.: know about free shipping offers –
    one of the better elements of on the web Black Friday
    shopping (regardless of without having to manage the chaos in stores)
    is getting your things transported free-of-charge. If you are done shopping and never qualify for
    free shipping, check how far away you will be from achieving that amount.
    TIP The second most suitable choice for an individual would be
    to use the internet.: usually do not wager on a table
    which includes the absolute minimum wager higher than five
    % of the budget. Easy and greatest on the web Blackjack Suggestion A clear downside of searching for women’s clothing online is
    not being in a position to try before you purchase.
    Better be safe than sorry.


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: