convert rows to columns

Hi All,

I have two tables

TblEmployee

EmpID,

EmpName

TblAddress

AddressID

EmpID

Address1

Address2

AddressType (will hold values like ‘TP’, ‘HP’ storing different address types)

If I do a simple join assuming 2 different address types exists for each employee. I would be getting two rows. But I need address1 and address2 of different address types as four columns I mean address1 of type ‘TP’, address2 of type ‘TP’, address1 of type ‘HP’, address2 of type ‘HP’

along with employee information.

Can some body help me with the query.select *
from TblEmployee e
inner join (
select EmpID,
TP_Address1 = max(case when AddressType = 'TP' then Address1 end),
TP_Address2 = max(case when AddressType = 'TP' then Address2 end),

HP_Address1 = max(case when AddressType = 'HP' then Address1 end),

HP_Address2 = max(case when AddressType = 'HP' then Address2 end)
from TblAddress
group by EmpID
) a
on e.EmpID = a.EmpID|||Thanks a lot K H Tan