title: SQL Unpivot the row and get Order Number
body: <p>I have a row with several diagnosis codes. How do I unpivot the columns into rows? I tried the below query to unpivot, but I'm not sure how to get the <code>DX_Order</code> which is the column number, eg, for <code>DX01</code>, <code>DX_Order</code>=1. I believe the NULLs are taken care by <code>UNPIVOT.</code></p>
  
  <p><strong>Current table</strong></p>
  
  <pre><code>+----+------+-------+-------+------+----+------+
  | ID | Name | DX01  | DX02  | DX03 | .. | DX25 |
  +----+------+-------+-------+------+----+------+
  |  1 | John | 426S3 | G2634 | NULL | .. | NULL |
  +----+------+-------+-------+------+----+------+
  </code></pre>
  
  <p><strong>Expected result</strong></p>
  
  <pre><code>+----+------+----------+-------+
  | ID | Name | DX_Order |  DX   |
  +----+------+----------+-------+
  |  1 | John |        1 | 426S3 |
  |  1 | John |        2 | G2634 |
  +----+------+----------+-------+
  </code></pre>
  
  <p><strong>Query</strong></p>
  
  <pre><code>SELECT ID, Name, DX
  FROM
  (
    SELECT ID, Name, DX01, DX02, DX03...DX25
    FROM dbo.Table
  ) AS cp
  UNPIVOT 
  (
    DX FOR DXs IN (DX01, DX02, DX03....DX25)
  ) AS up;
  </code></pre>