Given a table as under
OrderId OrderName WillShipToDeptId
1 A 1
1 B 2
2 X 1
2 Y 2
The expected output is
OrderId FirstOrder SecondOrder FirstOrderType SecondOrderType
1 A B 1 2
2 X Y 1 2
Solution First let us create the DDL to set up the enviroment
declare @t table(OrderId int,OrderName Varchar(10),WillShipToDeptId int)
Insert into @t
values(1,'A',1),(1,'B',2),(2,'X',1),(2,'Y',2)
select * From @t
The solution is as under
select
OrderId,
max(case WillShipToDeptId when 1 then OrderName end) as FirstOrder,
max(case WillShipToDeptId when 2 then OrderName end) as SecondOrder,
1 as FirstOrderType,
2 as SecondOrderType
from @t
group by OrderId