T-SQL help !

[John]

I have a table with four columns. First one is Email and then Pref1, Pref2, Pref3. Email column has duplicate addresses for those who opted - in more than one Prefs. With T-SQL, how can I have one row per Email with Opt-in in Pref columns and get rid of duplicate addresses.

example,
Email Pref1 Pref2 Pref3
abc@domain1.com yes Null Null
xyz@domain99.com Null yes yes
abc@domain1.com Null yes Null

Should turn into -

abc@domain1.com yes yes Null
xyz@domain99.com Null yes yes

Thanks a lot for your help.

John

[mak_999@yahoo.com]

Here we go Pal.
/********** start ***********/
create table xyz
(email varchar(10),pref1 char(4),pref2 char(4),pref3 char(4))


insert into xyz select "x@ccc.com",null,null,null
insert into xyz select "x@ccc.com","yes",null,null
insert into xyz select "x@ccc.com","yes",null,"yes&#3 4;
insert into xyz select "y@ccc.com",null,null,"yes"
insert into xyz select "z@ccc.com","yes","yes",&# 34;yes"
insert into xyz select "d@ccc.com","yes",null,null


select * from xyz
Result:

email pref1 pref2 pref3
---------- ----- ----- -----
x@ccc.com NULL NULL NULL
x@ccc.com yes NULL NULL
x@ccc.com yes NULL yes
y@ccc.com NULL NULL yes
z@ccc.com yes yes yes
d@ccc.com yes NULL NULL


create table temp_table(email varchar(10),pref char(4))
insert into temp_table select distinct email,pref1 from xyz where pref1 is not null
update a set a.pref1 = b.pref from xyz a,temp_table b where a.email=b.email
truncate table temp_table

insert into temp_table select distinct email,pref2 from xyz where pref2 is not null
update a set a.pref2 = b.pref from xyz a,temp_table b where a.email=b.email
truncate table temp_table


insert into temp_table select distinct email,pref3 from xyz where pref3 is not null
update a set a.pref3 = b.pref from xyz a,temp_table b where a.email=b.email
truncate table temp_table

drop table temp_table

select * from xyz
Result:

email pref1 pref2 pref3
---------- ----- ----- -----
x@ccc.com yes NULL yes
x@ccc.com yes NULL yes
x@ccc.com yes NULL yes
y@ccc.com NULL NULL yes
z@ccc.com yes yes yes
d@ccc.com yes NULL NULL

select distinct * from xyz
Result:

email pref1 pref2 pref3
---------- ----- ----- -----
d@ccc.com yes NULL NULL
x@ccc.com yes NULL yes
y@ccc.com NULL NULL yes
z@ccc.com yes yes yes

/**************End********************/



------------
John at 3/16/01 7:58:11 AM


I have a table with four columns. First one is Email and then Pref1, Pref2, Pref3. Email column has duplicate addresses for those who opted - in more than one Prefs. With T-SQL, how can I have one row per Email with Opt-in in Pref columns and get rid of duplicate addresses.

example,
Email Pref1 Pref2 Pref3
abc@domain1.com yes Null Null
xyz@domain99.com Null yes yes
abc@domain1.com Null yes Null

Should turn into -

abc@domain1.com yes yes Null
xyz@domain99.com Null yes yes

Thanks a lot for your help.

John

[John]

Thanks again, pal.

------------
mak_999@yahoo.com at 3/16/01 11:49:54 AM

Here we go Pal.
/********** start ***********/
create table xyz
(email varchar(10),pref1 char(4),pref2 char(4),pref3 char(4))


insert into xyz select "x@ccc.com",null,null,null
insert into xyz select "x@ccc.com","yes",null,null
insert into xyz select "x@ccc.com","yes",null,"yes&#3 4;
insert into xyz select "y@ccc.com",null,null,"yes"
insert into xyz select "z@ccc.com","yes","yes",&# 34;yes"
insert into xyz select "d@ccc.com","yes",null,null


select * from xyz
Result:

email pref1 pref2 pref3
---------- ----- ----- -----
x@ccc.com NULL NULL NULL
x@ccc.com yes NULL NULL
x@ccc.com yes NULL yes
y@ccc.com NULL NULL yes
z@ccc.com yes yes yes
d@ccc.com yes NULL NULL


create table temp_table(email varchar(10),pref char(4))
insert into temp_table select distinct email,pref1 from xyz where pref1 is not null
update a set a.pref1 = b.pref from xyz a,temp_table b where a.email=b.email
truncate table temp_table

insert into temp_table select distinct email,pref2 from xyz where pref2 is not null
update a set a.pref2 = b.pref from xyz a,temp_table b where a.email=b.email
truncate table temp_table


insert into temp_table select distinct email,pref3 from xyz where pref3 is not null
update a set a.pref3 = b.pref from xyz a,temp_table b where a.email=b.email
truncate table temp_table

drop table temp_table

select * from xyz
Result:

email pref1 pref2 pref3
---------- ----- ----- -----
x@ccc.com yes NULL yes
x@ccc.com yes NULL yes
x@ccc.com yes NULL yes
y@ccc.com NULL NULL yes
z@ccc.com yes yes yes
d@ccc.com yes NULL NULL

select distinct * from xyz
Result:

email pref1 pref2 pref3
---------- ----- ----- -----
d@ccc.com yes NULL NULL
x@ccc.com yes NULL yes
y@ccc.com NULL NULL yes
z@ccc.com yes yes yes

/**************End********************/



------------
John at 3/16/01 7:58:11 AM


I have a table with four columns. First one is Email and then Pref1, Pref2, Pref3. Email column has duplicate addresses for those who opted - in more than one Prefs. With T-SQL, how can I have one row per Email with Opt-in in Pref columns and get rid of duplicate addresses.

example,
Email Pref1 Pref2 Pref3
abc@domain1.com yes Null Null
xyz@domain99.com Null yes yes
abc@domain1.com Null yes Null

Should turn into -

abc@domain1.com yes yes Null
xyz@domain99.com Null yes yes

Thanks a lot for your help.

John