CODEWITHSUNDEEP
sqlsql-server
erik.ohlin
erik.ohlin

Reputation: 127

How to set aggregate bit value for a group

I need to "roll up" bit values for a group in my table via an UPDATE. So if one row in the group has a bit set to true, I need to set other rows in the group to true.

For example, I have a table like this:

GroupID | Type   | BitA | BitB
------------------------------
   1    | Parent |  0   |  0
   1    | Child  |  1   |  0
   2    | Parent |  0   |  0
   2    | Child  |  1   |  1
   2    | Child  |  0   |  1
   3    | Parent |  0   |  0
   3    | Child  |  0   |  0

The expected result after the UPDATE statement would then be:

GroupID | Type   | BitA | BitB
------------------------------
   1    | Parent |  1   |  0
   1    | Child  |  1   |  0
   2    | Parent |  1   |  1
   2    | Child  |  1   |  1
   2    | Child  |  0   |  1
   3    | Parent |  0   |  0
   3    | Child  |  0   |  0

Since Group 1 has a Child with BitA set to true, the Parent should also be set to true.

The only thing I've been able to find is casting the bit as an int first in order to aggregate it in the group, but it seems like I'm going down a rabbit hole.:

SELECT
   MAX(CAST(BitA as int)),
   MAX(CAST(BitB as int))
FROM MyTable
WHERE Type = 'Child'
GROUP BY GroupID

Is there an easier way to do this?

Upvotes: 1

Views: 55

Answers (2)

Excelnoobster
Excelnoobster

Reputation: 129

this could give you what you need ...

UPDATE M1
SET M1.BITA = 1,
    M1.BITB = 1
FROM MYTABLE M1
WHERE EXISTS
(
SELECT 1
  FROM MYTABLE M2
 WHERE M2.GroupID = M1.GoupID
      AND
     (
        M2.BITA = 1
       OR M2.BITB = 1
  )
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You seem to want to set the parent flags based on the children:

with toupdate as (
      select t.*,
             max(BitA) over (partition by groupid) as max_bitA,
             max(BitB) over (partition by groupid) as max_bitB
      from mytable t
     )
update toupdate
    set BitA = max_BitA,
        BitB = max_BitB
    where BitA <> max_BitA or BitB <> max_BitB;

If your bits are really typed as bit, you need some conversions:

with toupdate as (
      select t.*,
             convert(bit, max(convert(int, BitA)) over (partition by groupid)) as max_bitA,
             convert(bit, max(convert(int, BitB)) over (partition by groupid)) as max_bitB
      from mytable t
     )
update toupdate
    set BitA = max_BitA,
        BitB = max_BitB
    where BitA <> max_BitA or BitB <> max_BitB;

Upvotes: 2

Related Questions