• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

When We Use (coalesce) Statement

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
mubama0n
Asked:
mubama0n
1 Solution
 
HilaireCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now