title: Sort Columns For Dynamic Pivot
body: <p>I have the following SQL query where the columns created are out of order and I am not quite sure how to fix it.</p>
  
  <pre><code>SELECT rhead.rhcust AS [Cust ID], rdetl.rdextp AS [Inv Amt], rhead.rhivdt AS [Inv Date]
  INTO #TempTable
  FROM rhead
  LEFT OUTER JOIN rdetl
      ON rhead.rhinvc = rdetl.rdinvc
  WHERE rhead.rhivdt &gt;= '01-01-2012' AND rhead.rhivdt &lt;= '12-25-12'
  
  ALTER DATABASE Vista_TM SET COMPATIBILITY_LEVEL = 100
  
  DECLARE @cols AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX)
  
  select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                      from #TempTable
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
  
  set @query = 'SELECT [Cust ID],' + @cols + ' 
              from 
              (
                  SELECT [Cust ID], [Inv Amt],
                    ''Month''+cast(DATEPART(m, [Inv Date]) as varchar(2)) MonthNo
                  FROM #TempTable
              ) x
              pivot 
              (
                  sum([Inv Amt])
                  for MonthNo in (' + @cols + ')
              ) p '
  
  execute(@query)
  
  DROP TABLE #TempTable
  </code></pre>
  
  <p>I believe it has to do something with this section of the query:</p>
  
  <pre><code>select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                      from #TempTable
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
  </code></pre>
  
  <p>I did try messing around with the answer posted in this <a href="https://stackoverflow.com/questions/1122117/sql-dynamic-pivot-how-to-order-columns">topic</a> but I could not get the query to run.  I am hoping someone can help.</p>
  
  <p><strong>Edit</strong> I just noticed my rows are out of order and would like to sort by [Cust ID] as well.</p>