From: tfs@dslextreme.com   
      
   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   
      
   "tshad"  wrote in message   
   news:H4Gxo.5552$vM5.759@newsfe15.iad...   
   >I am doing a pivot where the sql is dynamic as I don't know the column   
   >names until done.   
   >   
   > But I am trying to get totals for my rows and totals for my columns (the   
   > pivoted columns).   
   >   
   > How would I do that if I have the following:   
   >   
   > I am using:   
   >   
   > SET @sql = '   
   > select * from #tfs   
   > PIVOT   
   > (   
   >    SUM(Number)   
   >    for retailer in('+@strList+')   
   > ) as pvt   
   > '   
   >   
   > To get.   
   >   
   > select * from #tfs   
   > PIVOT   
   > (   
   > SUM(Number)   
   > for retailer in([Sears], [CompUSA], [Lowes], [None], [K-Mart])   
   > ) as pvt   
   >   
   > Thanks,   
   >   
   > Tom   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)