CODEWITHSUNDEEP
sqlsql-serversql-server-2008
TonE
TonE

Reputation: 3035

SQL Aggregate bit columns to single bit result

I have some rows in the following format:

enter image description here

I want a SQL query which will group the above rows by EntityId and aggregate the bit columns in the following way:

I know I can do this by casting the bit column as an int and using Min/Max but it feels like a bit of a hack. I think I am having a slow day and missing the obvious solution...

What is the best way to do this?

I am using SQL Server 2008 R2, although a general SQL method would be best.

Update:

The desired result set for the rows above would be:

enter image description here

Upvotes: 6

Views: 1984

Answers (5)

Alex K.
Alex K.

Reputation: 175796

I think casting to an int is probably best overall as there are no bitwise aggregates, anything else will also be "hacky".

For fun this should work without casting the bit fields;

select
  EntityId,
  1 ^ count(distinct nullif(Submitted, 1)),
  1 ^ count(distinct nullif(Reviewed, 1)),
  count(distinct nullif(Query, 0))
from t
group by EntityId

Upvotes: 4

gvee
gvee

Reputation: 17161

For a bit of fun I threw this together. It doesn't use any casting or converting unlike previous answers...

DECLARE @tbl table (
   EntityId  int
 , Submitted bit
 , Reviewed  bit
 , Query     bit
);

INSERT INTO @tbl
  VALUES (36, 1, 0, 0)
       , (52, 1, 0, 0)
       , (52, 1, 1, 0)
       , (56, 1, 0, 0)
       , (56, 1, 1, 0)
       , (57, 1, 0, 0)
       , (57, 1, 1, 0)
;

SELECT DISTINCT
       base.EntityId
     , Coalesce(submitted.Submitted, 0) As Submitted
     , Coalesce(reviewed.Reviewed  , 0) As Reviewed
     , Coalesce(query.Query        , 1) As Query
FROM   @tbl As base
 LEFT
  JOIN @tbl As submitted
    ON submitted.EntityId = base.EntityId
   AND submitted.Submitted = 1
 LEFT
  JOIN @tbl As reviewed
    ON reviewed.EntityId = base.EntityId
   AND reviewed.Reviewed = 1
 LEFT
  JOIN @tbl As query
    ON query.EntityId = base.EntityId
   AND query.Query = 0
;

Results:

EntityId    Submitted   Reviewed    Query
----------- ----------- ----------- -----------
36          1           0           0
52          1           1           0
56          1           1           0
57          1           1           0

Upvotes: 0

Dave Mason
Dave Mason

Reputation: 4936

You want bits in the result, right?

SELECT
    t.EntityId,
    CAST(MIN(CAST(t.Submitted AS INT)) AS BIT) AS Submitted,
    CAST(MIN(CAST(t.Reviewed AS INT)) AS BIT) AS Reviewed,
    CAST(MAX(CAST(t.Query AS INT)) AS BIT) AS Query
FROM YourTable t
GROUP BY t.EntityId

Upvotes: 1

user1509107
user1509107

Reputation:

Relying on implicit data conversion:

select entityId
,max(1*submitted)  as submitted
,max(1*reviewed) as reviewed
,max(1*query) as query
from sd_test
group by entityId
order by entityId;

select entityId
,max(sign(submitted))  as submitted
,max(sign(reviewed)) as reviewed
,max(sign(query)) as query
from sd_test
group by entityId
order by entityId

Health Warning:

SIGN: Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

It works fine for bit data type though. Since bit can be either 0 or 1, sign will always return 1 or 0 (though in reality, it could return -1,0,1)

select sign(cast(1 as bit)); --returns:1
select sign(cast(0 as bit)); --returns:0

Upvotes: 3

Arion
Arion

Reputation: 31239

If I understand you correct you want something like this:

Test data:

DECLARE @tbl TABLE(EntityId INT,Submitted BIT, Reviewed BIT,Query BIT)
INSERT INTO @tbl VALUES
        (36,1,0,0),
        (52,1,0,0),
        (52,1,1,0),
        (56,1,0,0),
        (56,1,1,0),
        (57,1,0,0),
        (57,1,1,0)

Then a query like this:

SELECT
    table1.EntityId,
    MIN(CAST(Submitted AS INT)) AS Submitted,
    MIN(CAST(Reviewed AS INT)) AS Reviewed,
    MAX(CAST(Query AS INT)) AS Query
FROM
    @tbl as table1
GROUP BY
    table1.EntityId

Upvotes: 1

Related Questions