Pivoting in Sql Server 2000

Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 950
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


First let us create the DDL to set up the enviroment

declare @t table(OrderId int,OrderName Varchar(10),WillShipToDeptId int) 
Insert into @t
select * From @t

The solution is as under

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

Comments or Responses

Login to post response