title: Reorder columns in dynamic SQL PIVOT
body: <p>I am pivoting rows into columns using Dynamic SQL in order to account for an unknown number of columns.
  I am using SQL Server 16</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)
  
  select @cols = STUFF((SELECT ',' + QUOTENAME(PatientDiagnosis) 
                  from
                  (
                    select 'Diagnosis'
                            + cast(row_number() over(partition by LbPatientId
                                                      order by LbPatientId) as varchar(10)) PatientDiagnosis
                    from #Diag50
                    --ORDER  BY ',' + Quotename(PatientDiagnosis) DESC
                  ) d
                  group by PatientDiagnosis
                   ORDER  BY ',' + Quotename(PatientDiagnosis)                    
          FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)'),1,1,'')
  
  set @query = 'SELECT LbPatientId, ' + @cols + ' 
          from 
          (
              select LbPatientId, Diagnosis,
                ''Diagnosis''
                  + cast(row_number() over(partition by LbPatientId
                                            order by LbPatientId) as varchar(10)) PatientDiagnosis
              from #Diag50
          ) x
          pivot 
          (
              MIN(Diagnosis)
              for PatientDiagnosis in (' + @cols + ')
          ) p '
  
  execute sp_executesql @query;
  </code></pre>
  
  <p>This does pivot my data but the sort order for the Diagnosis columns is wrong.  </p>
  
  <p>It looks like this (there may be up to 50 columns for each LbPatientId)</p>
  
  <pre><code>LbPatientId|Diagnosis1|Diagnosis10|Diagnosis11|Diagnosis12...
  </code></pre>
  
  <p>-----------|----------|-----------|-----------|--------------
  1111       |Z95.5     |Z23        |Z13.89     |V85.1</p>
  
  <p>I want it to look like this:</p>
  
  <pre><code>LbPatientId|Diagnosis1|Diagnosis2 |Diagnosis3 |Diagnosis4..
  </code></pre>
  
  <p>-----------|----------|-----------|-----------|--------------
  1111       |Z95.5     |Z23        |Z13.89    </p>
  
  <p>I've tried various solutions I found for ordering columns, but it's still not working.  It's really the column headings that are important here as far as order goes.  Any help would be appreciated.</p>