title: Dynamic SQL Server Pivot Table
body: <p>I found a nice script that dynamically creates by column names for my pivot table, but I am not getting the assigned values back into the table.  Here is my starting table.</p>
  
  <pre><code>ORDER_ID    DSC_NAME        NAME
  ----------- --------------- -----------
  2           34-1500-XXX     DWG_DOC
  3           C0403           EQIP_1
  4           C4054           EQIP_2
  1           34-1500-013     PART
  0           88-0000         PRCS
  </code></pre>
  
  <p>I run this SQL to generate my columns that I want in my pivot table</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
  
  select @cols = STUFF((SELECT distinct 
             ',' + QUOTENAME(NAME)
                 FROM test
                 FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 
                 ,1,1,'')
  </code></pre>
  
  <p>This gives me the following output</p>
  
  <pre><code>[DWG_DOC],[EQIP_1],[EQIP_2],[PART],[PRCS]
  </code></pre>
  
  <p>When I run the dynamic SQL for the Pivot Table</p>
  
  <pre><code>SET @query = 'SELECT ' + @cols + ' from 
           (
              SELECT ORDER_ID,DSC_NAME
              FROM test
          ) x
          pivot 
          (
              MIN(ORDER_ID)
              for DSC_NAME in (' + @cols + ')
          ) p '
  
  execute(@query)
  </code></pre>
  
  <p>I see this result...</p>
  
  <pre><code>DWG_DOC     EQIP_1      EQIP_2      PART        PRCS
  ----------- ----------- ----------- ----------- -----------
  NULL        NULL        NULL        NULL        NULL
  </code></pre>
  
  <p>I have tried several different options, but I not come up with a solution to why this is not working.</p>
  
  <p>Desired Output would be where the column order is correct by the ORDER_ID</p>
  
  <pre><code>PRCS       PART           DWG_DOC        EQIP_1    EQIP_2    
  ---------- -------------- -------------- --------- ---------
  88-0000    34-1500-013    34-1500-XXX    C0403     C4054     
  </code></pre>
  
  <p>But this would also work my application as well.</p>
  
  <pre><code>DWG_DOC        EQIP_1    EQIP_2    PART           PRCS
  -------------- --------- --------- -------------- -----------
  34-1500-XXX    C0403     C4054     34-1500-013    88-0000
  </code></pre>