December 7, 2009 at 7:52 am
I have a table
[prospects]
id
companyname
address
city
zipcode
total_cars
Now in this table there are some duplicates
id companyname address city zipcode total_cars
1 test street NY 54543 NULL
2 test street NULL NULL 34
3 test street NY NULL NULL
4 test NULL NULL 54543 34
As you can see these 4 rows all contain a little bit of data. I want to merge these rows into 1 row with all available data:
test street NY 54543 34
The other rows should be deleted. Matching is based on the companyname.
How? And preferably I dont want to have each rowname in my SQL statement (because the table has way more rows than im showing here), I'd like a dynamic statement that goes through all available table rows.
Thanks!
December 7, 2009 at 9:30 pm
The following code will work, assuming the company name is consistent between all rows. For future reference, please include the declare statement and the inserts when you are describing your problem. A simple cut-and-paste (like you did) saves time for you, but wastes the time of the people whom you are asking to help you out. It's just a courtesy, but if you will take an additional couple of minutes, you
will find more people will look at your problem quicker.
declare @sample table (id int, companyName varchar(50), address varchar(50),
city varchar(50), zipcode int, total_cars int)
insert into @sample
select 1, 'test', 'street', 'NY', 54543, NULL union all
select 2, 'test', 'street', NULL, NULL, 34 union all
select 3, 'test', 'street', 'NY', NULL, NULL union all
select 4, 'test', NULL, NULL, 54543, 34
select companyName,max(address) as [address], max(city) as city, max(zipCode) as zipCode,
max(total_cars) as total_cars
from @sample
group by companyName
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 7, 2009 at 10:46 pm
The provided script will get you to the point of being able to merge the data. You will need to decide which record in the database to update with all of the correct data.
After you have updated the data, then you will want to check out a dedup cte script to remove the duplicate records.
One such script example is:
http://qa.sqlservercentral.com/scripts/Common+Table+Expression+(CTE)/62599/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2009 at 11:48 pm
I was assuming that it would be acceptable to just generate a new table from the old rather than do a series of updates using the result set. Sorry if I was mistaken in that assumption. Thanks for the catch, Jason.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply