Need a little sql help...
Hi all, My SQL mojo seems to have left me. Here is the situation, I have 1 table. In this table there are some times two rows that should have actually been one. Here is an example to explain. Table: Name, Age, address one, address two Row 1: Steve,40,123 Anystreet,NULL Row 2: Steve,40,NULL,456 Anystreet What should have been passed to me would have been: Steve,40,123 Anystreet,456 Anystreet The actual tables have a lot more columns and there are a few that this can happen with. The columns are either (null and not null values) or match exactly across rows. Any ideas what the SQL would look like to return a single row for each with all of the fields merged? Thanks, Steve ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341146 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need a little sql help...
How do you know the 2 Steve's are the same Steve? What if there was another Steve who was also 40 but lived elsewhere? Can you provide some more realistic data? And how you'd know they were the same Steve? or whomever? On Mon, Jan 24, 2011 at 12:57 PM, DURETTE, STEVEN J (ATTASIAIT) sd1...@att.com wrote: Hi all, My SQL mojo seems to have left me. Here is the situation, I have 1 table. In this table there are some times two rows that should have actually been one. Here is an example to explain. Table: Name, Age, address one, address two Row 1: Steve,40,123 Anystreet,NULL Row 2: Steve,40,NULL,456 Anystreet What should have been passed to me would have been: Steve,40,123 Anystreet,456 Anystreet The actual tables have a lot more columns and there are a few that this can happen with. The columns are either (null and not null values) or match exactly across rows. Any ideas what the SQL would look like to return a single row for each with all of the fields merged? Thanks, Steve ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341147 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Need a little sql help...
That was just example data... The Name is actually a distinct number letter combination. When the data is passed over to me they are supposed to pre-merge the rows into one, but they don't so I have been tasked to deal with it when the data suppliers don't. Another issue is that there are MANY more columns, so I could actually have 20 rows with this situation. They all merge into one though. Sorry for the confusion, I just redact true data whenever possible. -Original Message- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Monday, January 24, 2011 2:00 PM To: cf-talk Subject: Re: Need a little sql help... How do you know the 2 Steve's are the same Steve? What if there was another Steve who was also 40 but lived elsewhere? Can you provide some more realistic data? And how you'd know they were the same Steve? or whomever? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341148 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need a little sql help...
Steve, Is the name ultimately supposed to be the unique key for this table? Instead of battling with these multiple records, you could create a temporary table, loop through all the records in your main table and either insert or update a record in the temp table based upon the name. Once you have done that, delete all the rows from your main table and then insert the merged records from your temp table back into the main table. You should only need to run this scrubbing procedure once. Dean On Mon, Jan 24, 2011 at 2:13 PM, DURETTE, STEVEN J (ATTASIAIT) sd1...@att.com wrote: That was just example data... The Name is actually a distinct number letter combination. When the data is passed over to me they are supposed to pre-merge the rows into one, but they don't so I have been tasked to deal with it when the data suppliers don't. Another issue is that there are MANY more columns, so I could actually have 20 rows with this situation. They all merge into one though. Sorry for the confusion, I just redact true data whenever possible. -Original Message- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Monday, January 24, 2011 2:00 PM To: cf-talk Subject: Re: Need a little sql help... How do you know the 2 Steve's are the same Steve? What if there was another Steve who was also 40 but lived elsewhere? Can you provide some more realistic data? And how you'd know they were the same Steve? or whomever? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341149 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need a little sql help...
This will work, but I second the temp table idea.. select distinct name, age, addr1, addr2 from ( select f1.name, f1.age, case when f1.addr1 is null then f2.addr1 else f1.addr1 end as addr1, case when f1.addr2 is null then f2.addr2 else f1.addr2 end as addr2 from foouser f1 join foouser f2 on f1.name = f2.name ) where addr1 is not null and addr2 is not null On Mon, Jan 24, 2011 at 1:18 PM, Dean Lawrence dean...@gmail.com wrote: Steve, Is the name ultimately supposed to be the unique key for this table? Instead of battling with these multiple records, you could create a temporary table, loop through all the records in your main table and either insert or update a record in the temp table based upon the name. Once you have done that, delete all the rows from your main table and then insert the merged records from your temp table back into the main table. You should only need to run this scrubbing procedure once. Dean On Mon, Jan 24, 2011 at 2:13 PM, DURETTE, STEVEN J (ATTASIAIT) sd1...@att.com wrote: That was just example data... The Name is actually a distinct number letter combination. When the data is passed over to me they are supposed to pre-merge the rows into one, but they don't so I have been tasked to deal with it when the data suppliers don't. Another issue is that there are MANY more columns, so I could actually have 20 rows with this situation. They all merge into one though. Sorry for the confusion, I just redact true data whenever possible. -Original Message- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Monday, January 24, 2011 2:00 PM To: cf-talk Subject: Re: Need a little sql help... How do you know the 2 Steve's are the same Steve? What if there was another Steve who was also 40 but lived elsewhere? Can you provide some more realistic data? And how you'd know they were the same Steve? or whomever? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341150 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
