take Duplicated ID's out and Identify a new columns

Question

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 1

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;