SQL how to merge similar records into single row from same table?

Question

I'm currently trying to clean up a database of mailing subscribers that a former employee created. I've been able to consolidate and fix most issues (primarily duplicates), but I have instances of subscribers with duplicate records because they are subscribed to multiple regions. What I want to do is merge those duplicate records into 1.

Here is an redacted actual example of a duplicate record that I'd like to merge:

id     first     last    address    truck    machinery    gl    ne    nw
------------------------------------------------------------------------
1      Chuck     G....   12 Lorem   1                     1
2      Chuck     G....   12 Lorem            1                        1
3      Chuck     G....   12 Lorem            1                  1

And I'd like to merge the 2 into 1 record, and delete all duplicates (some have up to 9 duplicates) like this:

id     first     last    address    truck    machinery    gl    ne    nw
------------------------------------------------------------------------
1      Chuck     G....   12 Lorem   1        1            1     1     1

Gelatinate

What IF some rows contain different values in the same column, for example machinery=1,2,3 ? Which value must be picked ? – Recessional

In these cases, itll always only be a '1' or NULL. Its purpose is to simply define whether or not they subscribe to that edition. So if a '1' exists in that column anywhere in the duplicate rows, then '1' should be used. – Gelatinate

Answer 1

Use Group By and Max/Min Aggregate

SELECT id, 
       first, 
       last, 
       address, 
       Max(truck)     AS truck, 
       Max(machinery) AS machinery, 
       Max(gl)        AS gl, 
       Max(ne)        AS ne, 
       Max(nw)        AS nw 
FROM   yourtable 
GROUP  BY id, 
          first, 
          last, 
          address 

Carlyle

Brilliant. So simple. Using this kind of selection I was able to make a new list of subscribers without any duplicates, and have all the appropriate data merged. – Gelatinate

how would you approach this when the column is text? – Amandine