Databases
StackOverflow2013
Postsdbo
POCombining almost identical rows into 1
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCombining almost identical rows into 1
    primarykey
    data
    text
    <p>I have a tricky problem that I wouldn't mind a bit of help on, I've made some progress using queries that I've here and elsewhere, but am getting seriously stumped now.</p> <p>I have a mailing list that has numerous near duplications that I'm trying to combine into one meaningful row, taking data such as this.</p> <pre><code>Title Forename Surname Address1 Postcode Phone Age Income Ownership Gas Mrs D Andrews 122 Somewhere BH10 123456 66-70 Homeowner Ms Diane Andrews 122 Somewhere BH10 123456 £25-40 EDF </code></pre> <p>and making one row along the lines of </p> <pre><code>Title Forename Surname Address1 Postcode Phone Age Income Ownership Gas Mrs Diane Andrews 122 Somewhere BH10 123456 66-70 £25-40 Homeowner EDF </code></pre> <p>I have over 127 million records, most duplicated with a similar pattern, but no clear logic as was proven when I added an identity field. I also have over 90 columns to consider, so it's a bit of work! There isn't a clear pattern to the data, so I'm thinking I may have a huge <strong>case</strong> statement to try to climb over.</p> <p>Using the following code I can get a decent start on only returning the full name, but with the pattern of data - trying to compare the fields across rows is as follows.</p> <pre><code>SELECT c1.* FROM Mailing c1 JOIN Mailingc2 ON c1.Telephone1 = c2.Telephone1 AND c1.surname = c2.surname WHERE len(c1.Forename) &gt; len(c2.Forename) AND c2.over_18 &lt;&gt; '' AND c1.Telephone1 = '123456' </code></pre> <p>Has anyone got any pointers as to how I should progress please? I'm open to discussion and ideas...</p> <p>I'm using SQL 2005 and apologies in advance if the tagging is all over the place!</p> <p>Cheers,</p> <p>Jon</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJonTout
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CO. . There is a reason why third-party vendors and software exist to do householding and name matching. If you have a database with 120 million rows, then your company should invest in a real solution to this problem. SQL is not an adequate solution.
      singulars
      1. POCombining almost identical rows into 1
      1. USGordon Linoff
    2. COHi Gordon - I work for a data warehouse, we're looking to develop those type of solutions in-house. We do have external sources, but this is also proving to be a valuable learning process for everyone.
      singulars
      1. POCombining almost identical rows into 1
      1. USJonTout
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload