CODEWITHSUNDEEP
sqlms-access
VasoulaK
VasoulaK

Reputation: 9

Ms-access query to merge rows

I want a query to merge each of two rows meeting the following conditions in a large table:

  1. Surname, Name, FatherName are duplicates
  2. 1, 2 contain "---" on one of the rows and 3, 4 contain "---" on the other row

Example data:

Surname  Name   FatherName  Phone  Mobile   1     2     3     4
Smith    Alex   Nick        12345  00000   xxx   zzz   ---   ---
Smith    Alex   Nick        12345  00000   ---   ---   vvv   aaa
Jone     Mary   John        22222  11111   sss   eee   ---   ---
Pan      Peter  Peter       33333  22222   ttt   uuu   ---   ---
Bob      Nick   Nick        44444  77777   ---   ---   ppp   qqq
Mary     Maria  John        99999  00000   jjj   kkk   ---   ---
Mary     Maria  John        99999  00000   ---   ---   iii   ---

Expected output:

Surname  Name   FatherName  Phone  Mobile   1     2     3     4
Smith    Alex   Nick        12345  00000   xxx   zzz   vvv   aaa
Jone     Mary   John        22222  11111   sss   eee   ---   ---
Pan      Peter  Peter       33333  22222   ttt   uuu   ---   ---
Bob      Nick   Nick        44444  77777   ---   ---   ppp   qqq
Mary     Maria  John        99999  00000   jjj   kkk   iii   ---

Upvotes: 0

Views: 167

Answers (2)

Gustav
Gustav

Reputation: 56016

Try this simple query:

Select
    Surname,  
    Name,
    FatherName,
    Phone,
    Mobile,
    Max(T.[1]) As [1],
    Max(T.[2]) As [2],
    Max(T.[3]) As [3],
    Max(T.[4]) As [4]
From
    YourTable As T
Group By
    Surname,  
    Name,
    FatherName,
    Phone,
    Mobile

Upvotes: 3

forpas
forpas

Reputation: 164204

This will work for your sample data.
First with a self join I get the merged rows and then with UNION ALL for the unique rows:

SELECT t1.Surname, t1.Name, t1.FatherName, t1.Phone, t1.Mobile,
t1.[1], t1.[2], t2.[3], t2.[4]
FROM tablename t1 INNER JOIN tablename t2
ON t1.Surname = t2.Surname AND t1.Name = t2.Name AND t1.FatherName = t2.FatherName
AND (
  (t1.[1] <> '---' OR t1.[2] <> '---')
  AND 
  (t1.[3] = '---' AND t1.[4] = '---')
  AND 
  (t2.[3] <> '---' OR t2.[4] <> '---')
  AND
  (t2.[1] = '---' AND t2.[2] = '---')
)
UNION ALL
SELECT * FROM tablename AS t1
WHERE NOT EXISTS (
  SELECT 1 FROM tablename AS t2
  WHERE t1.Surname = t2.Surname AND t1.Name = t2.Name AND t1.FatherName = t2.FatherName AND t1.[1] <> t2.[1]
)

Upvotes: 0

Related Questions