Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

When We Use (coalesce) Statement

Posted on 2004-10-20
1
Medium Priority
?
252 Views
Last Modified: 2012-06-27
Hi..
I have a Data In The View (dbo.bor_by_province ) Is Like This :
Province      event type              Notype
 Middle        Civil Accedent            2                
 Middle        Old Building Fall         1
 Middle     No skills In Swimming    1                                                                                        
Western   Old Building Fall            4                                                                            
Western   Natural Fault                1
Eastern     Old Building Fall           1
Eastern     Civil Accedent              1                              

Note That The Data of Event Type (column) comes from the Table Events Which Is Like This:

EvSeq as int
Event type as nchar(60).

Note That This Table (Events) Is Not a static row table , It is (Dynamic) in nature lets take an Idea about it  from this out put:

1   Civil Accedent
2    Old Building Fall
3    No skills In Swimming
4    Technical Fault

May Be I'll add New Events in the futures .

The Data In The View (dbo.bor_by_province ) Is Like This :
Province      event type              Notype
 Middle        Civil Accedent            2                
 Middle        Old Building Fall         1
 Middle     No skills In Swimming    1                                                                                        
Western   Old Building Fall            4                                                                            
Western   Natural Fault                1
Eastern     Old Building Fall           1
Eastern     Civil Accedent              1                              

Note That The Data of Event Type (column) comes from the Table Events Which Is Like This:

EvSeq as int
Event type as nchar(60).

Note That This Table (Events) Is Not a static row table , It is (Dynamic) in nature lets take an Idea about it  from this out put:

1   Civil Accedent
2    Old Building Fall
3    No skills In Swimming
4    Technical Fault

May Be I'll add New Events in the futures .


The Out put Like This :

Province       Civil Accedent           Old Building Fall  ................................etc  
---------         --------------            ------------------
Middle             2                              1
Western        4                                0
Eastern          1                               1

I have the solution for this Question Please refere to this Link :
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21157805.html

I want to Know (Explain) How the (coalesce) statement works on the above Case , How It reaches all the records inside the (SQL) variable ? what is the role of (+) on this statement ? Why we used (from (select distinct event_type from dummmy_table_tescode) a) what do we mean by (a) ?

0
Comment
Question by:mubama0n
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 12356919
DECLARE @sql varchar(4000)

select @sql = coalesce(@sql + ', ', 'select province, ') + 'max(case when borcasetyp = ''' + borcasetyp + ''' then notype else 0 end) as [' + borcasetyp + ']'
from (select distinct borcasetyp from bor_by_province) a
set @sql = @sql + ' from bor_by_province group by province'
exec (@sql)
--print @sql


1) Coalesce basically returns the first not-null expression out of a commas-seperated list
coalesce(null, null, a, b, c) returns a, or b if a is null, or c if both a and b are evalutead as null

In this case coalesce is used to initialize the @sql variable on the first iteration.
When you declare a variable, it is set as null
so for the first borcasetyp found by the subquery,
@sql + ',' is evaluated as null and coalesce(@sql + ', ', 'select province, ') is evaluated as 'select province, '
for the second borcasetyp, @sql + ',' is not null anymore

The pseudo-code to decompose the logic could be as follows

for each @borcasetyp in subquery
   if @sql is null then
      @sql = 'select province, '
   else
      @sql = @sql + ', '
   end if
   @sql = @sql + 'max(case when borcasetyp = ' + @borcasetyp + ' then notype else 0 end) as [' +  @borcasetyp + ']'
next @borcasetyp

2) + is used to concatenate @sql with additional info in each and every "loop"

3) select distinct is to make an iteration per unique borcasetyp in the source table

4) the a is an alias for the subquery. It's mandatory to use an alias when you make a select statemtn on a subquery

I know the syntax is rather complex, and the same dynamic SQL could be computed with a cursor, but this version is much faster and I try to avoid cursors whenever I can.

Hilaire
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question