title: How to get rid of null value table A left outer join pivot table
body: <p>I want to set those NULL value to zero in result table. I can't find any solution so far. </p>
  
  <p>(A)</p>
  
  <pre><code>CREATE TABLE ##Temp1
   (
      tmp_id varchar(8),
      tmp_ac varchar(5),
      tmp_amt decimal(15,2)
   )
  
  Record :
  id   ac      amt
  1    30100   100.00
  2    30400   300.00
  3    30500   500.00
  </code></pre>
  
  <p>(B) create dynamic column pivot table:</p>
  
  <pre><code>declare @cols as varchar(max)
  declare @query as varchar(max)
  
  select @cols = ISNULL(@cols + ',','') + '[' + tmp_ac + ']' from ##Temp1 order by tmp_ac 
  
  set @query = 'select tmp_id, '+ @cols + ' into ##TempPV from 
                (
                    select tmp_id, tmp_ac, isnull(tmp_amt,0) as tmp_amt
                    from ##Temp1
                ) x
                pivot
                (
                   sum(tmp_amt)
                    for tmp_ac in (' + @cols + ')
                ) p'
  exec (@query)
  </code></pre>
  
  <p>(C)Left outer join table A to table pivot:</p>
  
  <pre><code>select
   ##TempA.f_id  as [ID],
  ##TempPV.*          
  from ##TempA
  left outer join  ##TempPV
  on  ##TempA.f_id = tmp_id
  order by f_id
  </code></pre>
  
  <p>(D) Result:</p>
  
  <pre><code>Id   30100    30400    30500
  1    100.00   NULL     NULL
  2    NULL     300.00   NULL
  3    NULL     NULL     500.00
  4    NULL     NULL     NULL
  5    NULL     NULL     NULL
  </code></pre>
  
  <p>(E) Expecting Result:</p>
  
  <pre><code>Id   30100    30400    30500
  1    100.00   0.00     0.00
  2    0.00     300.00   0.00
  3    0.00     0.00     500.00
  4    0.00     0.00     0.00
  5    0.00     0.00     0.00
  </code></pre>