From: tfs@dslextreme.com   
      
   @sql comes up with the following:   
      
   select * from #tfs   
   PIVOT   
   (   
    SUM(Number)   
    for retailer in([Sears], [CompUSA], [Lowes], [None], [K-Mart])   
   ) as pvt   
      
   Thanks,   
      
   Tom   
      
   "tshad"  wrote in message   
   news:uEFxo.187$st3.29@newsfe18.iad...   
   >I am using a pivot (dynamically) and am getting NULL results for some   
   >values.   
   >   
   > I want to change the NULLs to 0, but keep getting errors.   
   >   
   > In the following: @strList contains the pivot columns.   
   >   
   > This one works fine except for the nulls:   
   >   
   > SET @sql = '   
   > select * from #tfs   
   > PIVOT   
   > (   
   >    SUM(Number)   
   >    for retailer in('+@strList+')   
   > ) as pvt   
   > '   
   >   
   > I tried:   
   >   
   > SET @sql = '   
   > select * from #tfs   
   > PIVOT   
   > (   
   >    SUM(Number)   
   >    for ISNULL(retailer,0) in('+@strList+')   
   > ) as pvt   
   > '   
   > This gets me "incorrect syntax errors"   
   >   
   > I also tried   
   >   
   > SET @sql = '   
   > select * from #tfs   
   > PIVOT   
   > (   
   >    ISNULL(SUM(Number),0)   
   >    retailer in('+@strList+')   
   > ) as pvt   
   > '   
   > This one gets 'isnull' is not a recognized aggregate function.   
   >   
   > Thanks,   
   >   
   > Tom   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)