Solved

When We Use (coalesce) Statement

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Viewers will learn how the fundamental information of how to create a table.

752 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