July 13, 2010 at 11:11 pm
Hi All,
How to transform the rows data into columns, e.g I have one table with following structure and values;
EMP_IDContact_typeContact_Number
1Home 92 - 51 - 225478
1Cell 92 - 131 - 225478
1Office 92 - 51 - 325478
2Home 92 - 41 - 225478
2Cell 92 - 131 - 225478
2Office 92 - 41 - 325478
2Fax 93 - 41 - 325528
Now after transformation I want to see this in this way.
EMP_IDHome Cell Office Fax
192 - 51 - 22547892 - 131 - 22547892 - 51 - 325478
292 - 41 - 22547892 - 131 - 22547892 - 41 - 325478 93 - 41 - 325528
Thanks in advance for your help.
Shahbaz.
July 14, 2010 at 6:54 am
Simplest method
create table contacts (
EMP_ID int,
Contact_type varchar(20),
Contact_Number varchar(100))
insert into contacts
select 1, 'Home', '92 - 51 - 225478' union all
select 1, 'Cell', '92 - 131 - 225478' union all
select 1, 'Office', '92 - 51 - 325478' union all
select 2, 'Home','92 - 41 - 225478' union all
select 2, 'Cell', '92 - 131 - 225478' union all
select 2, 'Office', '92 - 41 - 325478' union all
select 2, 'Fax', '93 - 41 - 325528'
select emp_id,
max(case when Contact_type = 'home' then Contact_Number else '' end) as Home,
max(case when Contact_type = 'Cell' then Contact_Number else '' end) as Cell,
max(case when Contact_type = 'Office' then Contact_Number else '' end) as Office,
max(case when Contact_type = 'fax' then Contact_Number else '' end) as Fax
from contacts
group by emp_id
alternate method using pivot
select
emp_id,
home,
cell,
office,
fax
from (select * from contacts ) p
pivot (max(contact_number) for contact_type in (home,office,cell,fax)) pvt
and the pointless third way
select emps.emp_id,
home.Contact_Number as Home,
cell.Contact_Number as Cell,
office.Contact_Number as Office,
fax.Contact_Number as Fax
from (select distinct emp_id from contacts) emps
left outer join (
select emp_id, contact_number
from contacts
where Contact_type = 'home'
) as home
on emps.emp_id = home.emp_id
left outer join (
select emp_id, contact_number
from contacts
where Contact_type = 'cell'
) as cell
on emps.emp_id = cell.emp_id
left outer join (
select emp_id, contact_number
from contacts
where Contact_type = 'office'
) as office
on emps.emp_id = office.emp_id
left outer join (
select emp_id, contact_number
from contacts
where Contact_type = 'fax'
) as fax
on emps.emp_id = fax.emp_id
July 14, 2010 at 7:16 am
Now I spot the other thread :blush:
http://www.sqlservercentral.com/Forums/Topic952016-338-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply