From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Tue, 26 Oct 2010 12:37:02 -0700, 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   
   >   
   >Thanks,   
   >   
   >Tom   
      
   Hi Tom,   
      
   I'm not very familiar with the PIVOT syntax either (like Erland, I   
   prefer to roll my own). But I *think* you need to use COALESCE on the   
   outer query:   
      
   SELECT COALESCE([Sears], 0) AS [Sears],   
          COALESCE([CompUSA], 0) AS [CompUSA],   
          ...   
   FROM #tfs PIVOT (SUM (Number)   
                    FOR retailer IN ([Sears], [CompUSA], ...)   
                   ) AS pvt;   
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)