CODEWITHSUNDEEP
sql
HKLM
HKLM

Reputation: 41

SQL view and rename different values in a column into one another different name in value for correlated duplicate values

I have a table like this:

Pkey               KName            Group
-----------------------------------------------
PC-229             History         Ancient
PC-229             History         Self
PC-229             History         Load
PC-228             History         Ancient
PC-234             History         Load
PC-235             History         Self

I need a query to out put the above, the query need to output a view for the above and change the name of Group field values to "All" for the duplicated values in Pkey. so for above we have (PC-229 duplicated, but the correlated values in Group is different so I need them in one "All" value with only one return of PC-229), so I need the results something like below from above table;

Pkey               KName            Group
-----------------------------------------------
PC-229             History          All
PC-228             History         Ancient
PC-234             History         Load
PC-235             History         Self

Please advise!

Thanks

Upvotes: 2

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think this does what you want:

select pkey, history,
       (case when min(group) = max(group) then min(group)
             else '**All**'
        end) new_group
from t
group by pkey, history;

I'm not sure if history should be part of the aggregation. It always has the same value in your example, so you can remove it from the group by and use max(history) instead.

Upvotes: 1

Related Questions