title: SQL Dynamic Pivot - how to order columns
body: <p>I'm working on a dynamic pivot query on a table that contains:</p>
  
  <ul>
  <li>OID - OrderID </li>
  <li>Size - size of the  product</li>
  <li>BucketNum - the order that the sizes
  should go</li>
  <li>quantity - how   many ordered</li>
  </ul>
  
  <p>The size column contains different sizes depending upon the OID.</p>
  
  <p>So, using the code found <a href="http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx" rel="noreferrer">here</a>, I put this together:</p>
  
  <pre><code>DECLARE @listCol VARCHAR(2000)
  DECLARE @query VARCHAR(4000)
  
  SELECT  @listCol = STUFF(( SELECT distinct  '], [' + [size]
                             FROM     #t
                           FOR
                             XML PATH('')
                           ), 1, 2, '') + ']'
  
  
  SET @query = 'SELECT * FROM
        (SELECT OID,  [size], [quantity]
              FROM #t 
              ) src
  PIVOT (SUM(quantity) FOR Size
  IN (' + @listCol + ')) AS pvt'
  
  
  EXECUTE ( @query )
  </code></pre>
  
  <p>This works great except that the column headers (the sizes labels) are not in the order based upon the bucketnum column. The are in the order based upon the sizes. </p>
  
  <p>I've tried the optional Order By after the pivot, but that is not working.</p>
  
  <p>How do I control the order in which the columns appear?</p>
  
  <p>Thank you</p>