From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Wed, 27 Oct 2010 11:54:30 +0200, Hugo Kornelis wrote:   
      
   >Hi Tom,   
   >   
   >You need to add an ORDER BY at the end of the query:   
   >   
   >ORDER BY CASE WHEN Retailer = 'Grand Total' THEN 2 ELSE 1 END   
   >   
   >I also think there is something missing after the comma in the second   
   >union'ed query, but since you already got it working, I suppose that's a   
   >copy/paste error.   
      
   Correction. I just realised the first column will not be called   
   Retailer, as retailer is the pivot column. And since there are no   
   non-pivoted columns, your query will only produce grand totals to begin   
   with. If you want a row for (eg) each Product, plus a grand total, then   
   you'd need something like   
      
   select * from #tfs   
   PIVOT   
   (   
       ProductName,   
       SUM(Number)   
       for retailer in('+@strList+')   
   ) as pvt   
   UNION   
   select 'Grand Total', from (select Number, retailer from #tfs   
   PIVOT   
   (   
       SUM(Number)   
       for retailer in('+@strList+')   
   ) as pvt   
   ORDER BY CASE WHEN ProductName = 'Grand Total' THEN 2 ELSE 1 END;   
      
   Oh, and you can probably use UNION ALL instead of UNION, that would   
   perform a bit better.   
      
   --   
   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)