Assuming you want the
DX
values in ascending order, something like this should work:
WITH cte As
(
SELECT
MRN,
DX,
ROW_NUMBER() OVER (PARTITION BY MRN ORDER BY DX) As RN
FROM
[mydb].[dbo].[mytable]
)
SELECT
MRN,
Max(CASE RN WHEN 1 THEN DX END) As DX1,
Max(CASE RN WHEN 2 THEN DX END) As DX2,
Max(CASE RN WHEN 3 THEN DX END) As DX3,
Max(CASE RN WHEN 4 THEN DX END) As DX4,
Max(CASE RN WHEN 5 THEN DX END) As DX5,
Max(CASE RN WHEN 6 THEN DX END) As DX6,
Max(CASE RN WHEN 7 THEN DX END) As DX7,
Max(CASE RN WHEN 8 THEN DX END) As DX8,
Max(CASE RN WHEN 9 THEN DX END) As DX9,
Max(CASE RN WHEN 10 THEN DX END) As DX10,
Max(CASE RN WHEN 11 THEN DX END) As DX11,
Max(CASE RN WHEN 12 THEN DX END) As DX12,
Max(CASE RN WHEN 13 THEN DX END) As DX13,
Max(CASE RN WHEN 14 THEN DX END) As DX14,
Max(CASE RN WHEN 15 THEN DX END) As DX15,
Max(CASE RN WHEN 16 THEN DX END) As DX16,
Max(CASE RN WHEN 17 THEN DX END) As DX17,
Max(CASE RN WHEN 18 THEN DX END) As DX18,
Max(CASE RN WHEN 19 THEN DX END) As DX19,
Max(CASE RN WHEN 20 THEN DX END) As DX20
FROM
cte
GROUP BY
MRN
;
Given your sample data, this will produce:
MRN | DX1 | DX2 | DX3 | DX4 | ...
-----------------------------------------
123 | a105 | b27 | NULL | NULL | ...
124 | 1566A | a106 | b27 | NULL | ...