SQL query help needed for sql retard.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query help needed for sql retard.

Author

Topic

scottlucas58
Starting Member

8 Posts

Posted - 2009-01-22 : 23:02:51

I have a table with some semi-duplicate rows i need to merge.

name email url phone
----- ----- --- -----
ABC distributors null www.abcd.com null
ABC distributors null www.abcd.com 123-123-1234
ABC distributors info@abcd.com www.abcd.com null

obviously it would be nice to have:
name email url phone
----- ----- --- -----
ABC distributors info@abcd.com www.abcd.com 123-123-1234

and be rid of the extra rows. Just not sure how to accomplish with sql. Don't really want to write an application and loop through looking at everything. THis seems like it would be a common issue, but i can't seem find or solve it for myself. any help is appreciated. thanks

-s

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 23:11:21

try this
declare @tab table(name varchar(32), email varchar(32),url varchar(32),phone varchar(32))
insert into @tab select 'ABC distributors','null','www.abcd.com','null' union all select
'ABC distributors','null','www.abcd.com','123-123-1234' union all select
'ABC distributors','info@abcd.com','www.abcd.com','null'

select name,min(email) as email ,url,min(phone) as phone from @tab group by name , url

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-22 : 23:13:09

declare @temp table (name varchar(32),email varchar(32), url varchar(32), phone varchar(32))
insert into @temp
select 'ABC distributors',null, 'www.abcd.com', null union all
select 'ABC distributors', null, 'www.abcd.com' ,'123-123-1234' union all
select 'ABC distributors', 'info@abcd.com', 'www.abcd.com', null

select name,max(email) email,max(url) url ,max(phone) phone from @temp group by name

output: ABC distributors info@abcd.com www.abcd.com 123-123-1234

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 23:30:52

ho do you want to merge if you've row like below also?

select 'ABC distributors', 'info@pqrs.com', 'www.pqrs.com', null

scottlucas58
Starting Member

8 Posts

Posted - 2009-01-23 : 09:08:35

Sorry, i guess i wasn't clear enough. THat is just one example. There are 106,000 other similar records, with different "name" pairs. I can't do queries with specific field text or i will never get done.

name email url phone
---- ----- --- -----
ABC distributors null www.abcd.com null
ABC distributors null www.abcd.com 123-123-1234
ABC distributors info@abcd.com www.abcd.com null
DEF retail jb@def.com null 333-444-5555
DEF retail jb@def.com www.def.com null
HIJ wholesale null www.hij.com null
HIJ wholesale web@hij.com null 666-777-8888
....
luckily for me, the fields are in the right order. hopefully this makes more sense now,
Thank you bklr, Nageswar9, and visakh16 for your prompt responses.

-s

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-23 : 09:38:52

visakh's question was, what is the expected output for THIS set of data?

select 'ABC distributors',null, 'www.abcd.com', null union all
select 'ABC distributors', null, 'www.abcd.com' ,'123-123-1234' union all
select 'ABC distributors', 'info@abcd.com', 'www.abcd.com', null
select 'ABC distributors', 'info@pqrs.com', 'www.pqrs.com', null

Notice that there are TWO DIFFERENT email addresses, and TWO DIFFERENT urls for ONE Name. How would you handle that?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:41:39

yup...exactly...how do you want output in such case?

scottlucas58
Starting Member

8 Posts

Posted - 2009-01-23 : 11:07:03

Actually, there are not any different email, urls, etc.
One row never has email and rarely url.
When both have data in field, they are the same.
I stripped special charaters out of phone to make them identical if both rows has phone.
even if both rows end up identical, would they be easy to find and remove.
I still have the data in separate tables if that is easier.
I did a UNION to add them to one, thus my current problem.
I told you i was a sql retard. THanks again.

-s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:20:17

quote:
Originally posted by scottlucas58

Actually, there are not any different email, urls, etc.
One row never has email and rarely url.
When both have data in field, they are the same.
I stripped special charaters out of phone to make them identical if both rows has phone.
even if both rows end up identical, would they be easy to find and remove.
I still have the data in separate tables if that is easier.
I did a UNION to add them to one, thus my current problem.
I told you i was a sql retard. THanks again.

-s

why dont you group by name and take max() of otyher values while inserting to avoid these nulls?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources