From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Tue, 26 Oct 2010 15:32:16 -0700, tshad wrote:   
      
   >I finally got this to work using the following:   
   >   
   >SET @sql = '   
   >select * from #tfs   
   >PIVOT   
   >(   
   >    SUM(Number)   
   >    for retailer in('+@strList+')   
   >) as pvt   
   >UNION   
   >select 'Grand Total', from #tfs   
   >PIVOT   
   >(   
   >    SUM(Number)   
   >    for retailer in('+@strList+')   
   >) as pvt   
   >'   
   >The problem is that the 2nd Select after the UNION is being put at the 3rd   
   >row instead of at the bottom???   
   >   
   >How do you get it to put the results of the 2nd select after the 1st   
   >select???   
   >   
   >Thanks,   
   >   
   >Tom   
      
   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.   
      
   --   
   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)