Solved

When We Use (coalesce) Statement

Posted on 2004-10-20
1
241 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
1 Comment
 
LVL 26

Accepted Solution

by:
Hilaire earned 125 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now