TSQL - Using pivot to adjust results

Occasional Contributor

Hi All, In my sql table, I have a results like this ..

TheTallman_0-1626974649216.png

 

but in requirement, i need output in this way ..

 

TheTallman_0-1626975040946.png

 

 

 

2 Replies

@TheTallman , I can't see anything in your "micro"-screenshots.

Please post table design as DDL, some sample data as DML statement and the expected result.

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