CODEWITHSUNDEEP
sql-server
perumadan
perumadan

Reputation: 73

Assign name to other rows upon group condition

I have a dataset which is of following nature. I would like to replace the names in "MAKE" column if the column contains "PQR" per unique country.

   country     MAKE
1      USA      PQR
2      USA      ABC
3       UK      PQR
4       UK      DEF
5      JPN      DEF
6      JPN      LMN

Desired Output:

   country    MAKE
1      USA    PQR
2      USA    PQR
3       UK    PQR
4       UK    PQR
5      JPN    OTHERS
5      JPN    OTHERS

Upvotes: 2

Views: 43

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

One option is conditional aggregation with analytic functions:

SELECT
    country,
    CASE WHEN SUM(CASE WHEN MAKE = 'PQR' THEN 1 ELSE 0 END) OVER (PARTITION BY country) > 0
         THEN 'PQR' ELSE 'OTHERS' END AS MAKE
FROM yourTable
ORDER BY
    country;

enter image description here

Demo

Upvotes: 3

Related Questions