Use Dynamic Query with Pivot... try some thing like this...
Declare @Id Int, @Sql varchar(400),@Cols varchar(40)
Create Table #Temp
(
Order_Id Int,
InventoryId Int,
ItemType Varchar(40),
total Int
)
Insert into #Temp
Values(1,5,'Orange',5000),(1,4,'Apple',3000),(2,1,'Mango',3400),(2,5,'Orange',1700)
Set @Id=2
Set @Cols= Stuff((Select Distinct '],['+ItemType From #Temp Where Order_Id=@Id
for Xml Path(''),Type).value('.','VARCHAR(Max)'), 1, 2,'')+']'
Set @Sql= 'Select '+@Cols + 'From (Select ItemType,total From #Temp Where Order_Id='
+ Cast(@Id as Varchar) +')st pivot (Sum(total) For ItemType in ('
+ @Cols + '))Pvt'
Exec(@Sql)
Drop Table #Temp