title: CASE WHEN to add commas only when there is a value
body: <p>I have a multiple fields that needs to be put into a string only when there is something in the field and on top of that I don't want the commas between fields if they are null.  I tried using case when but am clearly doing something wrong and would appreciate some help with the following:</p>
  
  <pre><code>USE newCityCollection
  
  UPDATE PropertyInformationDump
  SET    RegistryAdd = (CASE
                          WHEN b.OCAREOF IS NULL THEN Isnull(b.OCAREOF, '')
                          ELSE b.OCAREOF + ' ,'
                        END) + (CASE
                                  WHEN b.O1STADD IS NULL THEN Isnull(b.O1STADD, '')
                                  ELSE b.O1STADD + ' ,'
                                END) + (CASE
                                          WHEN b.O2NDADD IS NULL THEN Isnull(b.O2NDADD, '')
                                          ELSE b.O2NDADD + ' ,'
                                        END) + (CASE
                                                  WHEN b.OSTNAME IS NULL THEN Isnull(b.OSTNAME, '')
                                                  ELSE b.OSTNAME + ' ,'
                                                END) + (CASE
                                                          WHEN b.OCITYST IS NULL THEN Isnull(b.OCITYST, '')
                                                          ELSE b.OCITYST + ' ,'
                                                        END) + (CASE
                                                                  WHEN b.OZIP IS NULL THEN Isnull(b.OZIP, '')
                                                                  ELSE b.OZIP + ' ,'
                                                                END)
  FROM   dbo.vw_BRT b
  WHERE  BRTNumber = b.PARCEL
  </code></pre>
  
  <p>When I execute that on a field I get nada and am not sure why which is likely a fundemental misunderstanding of how <code>CASE</code> works. I did make sure those fields were actually null and not empty so that is not the problem.</p>