From: tfs@dslextreme.com   
      
   "Lennart Jonsson"  wrote in message   
   news:ia7cep$j1h$1@news.eternal-september.org...   
   > On 2010-10-26 21:37, tshad wrote:   
   >> @sql comes up with the following:   
   >>   
   >> select * from #tfs   
   >> PIVOT   
   >> (   
   >>  SUM(Number)   
   >>  for retailer in([Sears], [CompUSA], [Lowes], [None], [K-Mart])   
   >> ) as pvt   
   >>   
   >   
   > I would try something like:   
   >   
   > select * from #tfs   
   > PIVOT   
   > (   
   >   SUM(coalesce(Number, 0))   
   >   for retailer in([Sears], [CompUSA], [Lowes], [None], [K-Mart])   
   > ) as pvt   
   >   
      
   I tried that.  I get the same error I got for ISNULL:   
      
   Incorrect syntax near the keyword 'coalesce'.   
      
   > Not sure whether coalesce is coalesce or something else in sql-server   
   > though. The function should return the leftmost argument that is not   
   > null, in this case if Number is null -> 0.   
   >   
   > Since I don't know the pivot function, there is a possibility that the   
   > null you see origins from it, and if that is the case the solution above   
   > wont work.   
   >   
      
   That is what I see   
      
   I would think that would be a real problem if you want 0 to show up, such as   
   for copying to a spreadsheet.   
      
   Tom   
   > /Lennart   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)