Pedram Salamati Pedram Salamati - 2 months ago 6
SQL Question

take Duplicated ID's out and Identify a new columns

I Joined 6 table together to gather all information that I need.
I want all Id's, Names, Birthdays, and Ethnicity.
Some Ids have 2 or more Ethnicity and that will cause a id be duplicated.
I am thinking of writing a sub query or can I just use a case statement since I have tried case statement before and works for another case but I can not apply it in this case.

what I have is:

ID NAME Birthdays Ethnicity
4000 Pedram 11/11/1999 Middle East
4001 Carlos 11/11/1920 Spanish
4001 Carlos 11/11/1920 Native American
4002 Asia 11/22/1986 Polish
4002 Asia 11/22/1986 Native American
4002 Asia 11/22/1986 White/caucassian


I want to say if any Id duplicated and ethnicity is different <> just give me this:

ID NAME Birthdays Ethnicity
4000 Pedram 11/11/1999 Middle East
4001 Carlos 11/11/1920 Multiracial
4002 Asia 11/22/1986 multiracial


PS : ethnicity is in a different table and I joined it to Person_table
PS : to be able to join ethnicity table to Person_table I needed to join 3 more tables that have pr keys that can related to each other.
PS : I tried CASE WHEN Count (Id) > 1 THEN 'Multiracial' ELSE Ethnicity END AS Ethnicity_2
and it Identify all ethnicity as Multiracial.

Any help Or thought will be appreciate.

Answer

You can use this:

WITH CTE AS
(
    SELECT  *,
            N = COUNT(*) OVER(PARTITION BY ID),
            RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Ethnicity)
    FROM dbo.YourTable
)
SELECT  ID,
        NAME,
        Birthdays,
        CASE WHEN N > 1 THEN 'Multiracial' ELSE Ethnicity END Ethnicity
FROM CTE
WHERE RN = 1;