TSQL - Using pivot to adjust results

Hi,
You can use below T-SQL, if you have fix 6 columns for order sequence from 1 to 6.
SELECT [Identity No] AS medical_claims_line_Id, ISNULL([Order_Code_1],0) AS [Order_Code_1],
ISNULL([Order_Code_2],0) AS [Order_Code_2], ISNULL([Order_Code_3],0) AS [Order_Code_3],ISNULL([Order_Code_4],0) AS [Order_Code_4],ISNULL([Order_Code_5],0) AS [Order_Code_5],ISNULL([Order_Code_6],0) AS [Order_Code_6]
FROM (
SELECT [Identity No], 'Order_Code_' + CAST(Order AS VARCHAR) AS Order, [Order Code]
FROM table
) AS tbl
PIVOT ( MAX([Order Code] )
FOR Order IN (
[Order_Code_2],[Order_Code_3],[Order_Code_4],[Order_Code_5],[Order_Code_6]
)
) AS pvt