Code Snippet posted by:
Niladri.Biswas | Posted on: 6/18/2012 | Category:
SQL Server Codes | Views: 393 | Status:
[Member] |
Points: 40
|
Alert Moderator
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
Best Regards,
Niladri Biswas