Crosstab querry with a twist

[Wouter HM]

Hi There,

I have simplified my problem as much as possible.
I am using SQL Server 2005.
In the samples I us the ‘#’ as column separator

I have a table with three columns

ObjectId varchar(20),
TypeId int,
DateIssued, dateTime

The typeId can only contain 1, 2 or 3.

Filled with something like

ObjectId # TypeId # DateIssued
Cheap # 1 # 2009-11-13
Cheap # 2 # 2009-12-10
Monkey # 1 # 2009-12-12
Cheap # 3 # 2009-12-14
Monkey # 2 # 2009-12-20
Monkey # 3 # 2009-12-24

I need a result as:

ObjectId # One # Two # Three
Cheap # 2009-11-23 # 2002-12-10 # 2009-12-14
Monkey # 2009-12-12 # 2009-12-20 # 2009-12-24

How can I get the result I need?


Thanks in advance.

[Wouter HM]

Thank to all who have taken a moment to look into this problem.

I have found a solution:

select A.ObjectId,
A.DateIssued as One,
B.DateIssued as Two,
C.DateIssued as Three
from ((Select ObjectId, DateIssued
from tabMonstername where
TypeId = 1) A
left join
(Select ObjectId, DateIssued
from tabMonstername
where TypeId = 2) B
on A.ObjectId = B.ObjectId)
left join
(Select ObjectId, DateIssued
from tabMonstername
where TypeId = 3) C
on A.ObjectId = C.ObjectId