Solved

When We Use (coalesce) Statement

Posted on 2004-10-20
1
242 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

932 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

15 Experts available now in Live!

Get 1:1 Help Now