sqlsql-serverdatabasessms-17ssms-16

Make a master record out of several duplicate records for each id

My table structure is as shown below

Id | Name | City   | Country | State
01 | Bob  | *NY*   | null    | null
01 | Bob  | null   | *US*    | null
01 | Bob  | null   | null    | *AL*   
02 | Roy  | *LA*   | null    | null
02 | Roy  | null   | *IN*    | null
02 | Roy  | null   | null    | *MG*

I want to generate two output records from the above table like below.

Id | Name | City |Country | State
01 | bob  | NY   |   US   |   AL
02 | Roy  | LA   |   IN   |   MG
Solution

  • You can use aggregation:

    select id, name, max(city), max(country), max(state)
from t
group by id, name;