CODEWITHSUNDEEP
sqlsql-serversql-server-2012case-statement
Pedram Salamati
Pedram Salamati

Reputation: 137

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.

Upvotes: 1

Views: 133

Answers (3)

Matt
Matt

Reputation: 14341

SELECT
    id, name, Birthdays,
    IIF(COUNT(DISTINCT Ethnicity) > 1, 'Multiracial', MIN(Ethnicity)) as Ethnicity
FROM
    Table
GROUP BY
    id, name, Birthdays

SELECT
    id, name, Birthdays,
    CASE WHEN COUNT(DISTINCT Ethnicity) > 1 THEN 'Multiracial' ELSE MIN(Ethnicity) END as Ethnicity
FROM
    Table
GROUP BY
    id, name, Birthdays

Upvotes: 1

kjmerf
kjmerf

Reputation: 4345

This one might not be the most efficient but it works. Just substitute your derived table for t below:

SELECT DISTINCT t.id, t.name,
CASE WHEN cnt = 1 THEN ethnicity
ELSE 'Multiracial' END AS ethnicity
FROM t
INNER JOIN
(SELECT id, COUNT(DISTINCT ethnicity) AS cnt
 FROM t
 GROUP BY id) sub
ON t.id = sub.id

Tested here: http://sqlfiddle.com/#!9/7473f/6

Upvotes: 1

Lamak
Lamak

Reputation: 70648

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;

Upvotes: 2

Related Questions