SQL Unpivot the row and get Order Number

Question

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 DX_Order which is the column number, eg, for DX01, DX_Order=1. I believe the NULLs are taken care by UNPIVOT.

Current table

+----+------+-------+-------+------+----+------+
| ID | Name | DX01  | DX02  | DX03 | .. | DX25 |
+----+------+-------+-------+------+----+------+
|  1 | John | 426S3 | G2634 | NULL | .. | NULL |
+----+------+-------+-------+------+----+------+

Expected result

+----+------+----------+-------+
| ID | Name | DX_Order |  DX   |
+----+------+----------+-------+
|  1 | John |        1 | 426S3 |
|  1 | John |        2 | G2634 |
+----+------+----------+-------+

Query

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;

Answer 1

Use Cross Apply instead of unpivot. Along with DX0n columns you can add the order column as well

SELECT ID, Name, DX_Order, DX
  FROM dbo.Table
  Cross apply (values (DX01,1),(DX02,2),(DX03,3),...(DX025,25)) cs (DX, DX_Order)
  Where DX is not null

When there are some NULL values in DX0n columns and still you want the DX_Order to be sequence then

SELECT ID,
       NAME,
       DX_Order = Row_number()OVER(ORDER BY DX_Order),
       DX
FROM   (SELECT ID, NAME, DX_Order, DX
        FROM   dbo.Table
               CROSS apply (VALUES (DX01,1),(DX02,2),(DX03,3),..(DX025,25)) cs (DX, DX_Order)
        WHERE  DX IS NOT NULL) a