CODEWITHSUNDEEP
sqlsql-servert-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

CASE and GROUP rows with same values?

I'm trying to group the following 4 rows into 2 rows, by taking all rows with equal pgroup and getting an overall GeneralStatus using the GeneralStatus of rows with Unit 50 or 60.

In the example below, the first 2 rows are a group, since both are GB002 with pgroup 1 (Alpha), and they have either 60 or 50. As you can see, one is ENABLED while the other is DISABLED. So this means that GB002 Alpha is DISABLED. In the other example, GB002 w/ pgroup 3 (Gamma), both rows (Unit 60 & 50) are ENABLED, soGB002 Gammais ENABLED.

Basically, the final result should be:

GB002,Alpha, DISABLED
GB002,Gamma, ENABLED

This is what I've tried, but I can't seem to get it:

declare @table table 
(
    ID varchar(10),
    pgroup varchar(5),
    Unit varchar(5),
    GeneralStatus varchar(10)
)

insert into @table (id, pgroup, unit, GeneralStatus) select 'GB002',1, 60, 'ENABLED'
insert into @table (id, pgroup, unit, GeneralStatus) select 'GB002',1, 50, 'DISABLED'
insert into @table (id, pgroup, unit, GeneralStatus) select 'GB002',3, 60, 'ENABLED'
insert into @table (id, pgroup, unit, GeneralStatus) select 'GB002',3, 50, 'ENABLED'
--select *From @table

select 
id,
case 
    when pgroup = 1 then 'Alpha'
    when pgroup = 2 then 'Beta'
    when pgroup = 3 then 'Gamma'
END, 
case 
    when (SELECT COUNT(*) FROM @table t2 
          WHERE t1.ID=t2.ID
          AND t1.Unit=t2.Unit
          AND GeneralStatus = 'ENABLED') >= 2 then 'ENABLED'
    ELSE 'DISABLED'
end GeneralStatus
from @table t1  where unit in (60,50)

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Is this what you want?

select id, pgroup,
       (case when min(GeneralStatus) = max(GeneralStatus) and
                  max(GeneralStatus) = 'Enabled'
             then 'Enabled'
             else 'Disabled'
        end) as GeneralStatus
from @table t
where unit in (50, 60)
group by id, pgroup;

It assigns Enabled only when all the status values are 'Enabled'.

If you know the values are always 'Enabled' and 'Disabled', you can use this hack:

select id, pgroup, min(GeneralStatus) as GeneralStatus
from @table t
where unit in (50, 60)
group by id, pgroup;

Upvotes: 1

Related Questions