CODEWITHSUNDEEP
sqlsql-servert-sqlcase
user10611398
user10611398

Reputation: 21

How to use case statement in group by category in SQL Server

I have a following table

Id   Values    
 1     A          
 1     A          
 1     B          
 2     @          
 2     @
 2     @
 3     A
 3     A
 3     A
 3     A
 3     @
 4     B
 4     B
 4     B

Output:

Id   Values
 1     @
 2     @
 3     A
 4     B

Within each Id group, if all values are @, I want to set the value for that Id to @ else if all values for Id are same (ignoring @s) (e.g. all As), set the value for that Id to be that value (A) else set value of id to @.

How do I run that CASE statement to give one unique value to each Id in SQL Server?

New to SQL Server, please help.

Upvotes: 0

Views: 186

Answers (1)

Squirrel
Squirrel

Reputation: 24793

use GROUP BY on ID and check for MIN () and MAX (). If min = max means, all value are the same.

For you case the twist is the @. It should be ignore. The trick is to use NULLIF() on @

select  id,
        case    when    min(NULLIF([Values], '@')) = max(NULLIF([Values], '@'))
                and     min(NULLIF([Values], '@')) <> '@'
                then    min(NULLIF([Values], '@'))
                else    '@'
                end
from    yourtable
group by id

Upvotes: 1

Related Questions