CODEWITHSUNDEEP
sqlsql-server
Vinod
Vinod

Reputation: 566

Aggregating BIT values into a single row

SQL tableA have 4 rows of Data, with 3 columns.

enter image description here

Expected Result is :

enter image description here

Expected Logic is : In SectorTestRequiured is having two TRUE value column data so expected is TRUE. ( if alteat one column value is true , expected o/p should be true)

From given example :

SectorDetailsRequired column has all Zero values rows hence expected o/p is FALSE.

SectorTestRequired have one or more True value column data so o/p is TRUE.

OtherInfoRequired have all the column data as True so o/p is TRUE.

NOTE: I am using BIT Values , so MAX, SUM won't work as provided in answers given below.

Upvotes: 0

Views: 53

Answers (2)

Crowcoder
Crowcoder

Reputation: 11514

Sum the columns and apply logic based on what you need the sum to be. I'm not sure I interpreted your requirements correctly, but this should give you a clue what to do:

SELECT
   CASE SUM(CAST(SectorDetailsRequired AS int)) WHEN 0 THEN 0 ELSE 1 END AS SectorDetailsRequired,
   CASE SUM(CAST(SectorTestRequired AS int)) WHEN >= 1 THEN 1 ELSE 0 END AS SectorTestRequired,
   CASE SUM(CAST(OtherInfoRequested AS int)) WHEN 4 THEN 1 ELSE 0 AS END OtherInfoRequested
FROM <your table>

Upvotes: 0

Tanner
Tanner

Reputation: 22753

I've understood your question to require a column has 2 TRUE values in order to output a TRUE value in the aggregated results, so I COUNT the TRUE values and used CASE WHEN to output 1 or 0 depending on the count being >=2:

CREATE TABLE #tmp
    (
      SectorDetailsRequired BIT ,
      SectorTestRequired BIT ,
      OtherInfoRequired BIT
    )

INSERT  INTO #tmp
        ( SectorDetailsRequired, SectorTestRequired, OtherInfoRequired )
VALUES  ( 0, 1, 1 ),
        ( 0, 1, 1 ),
        ( 0, 0, 1 ),
        ( 0, 0, 1 )

SELECT  CASE WHEN COUNT(NULLIF(SectorDetailsRequired, 0)) >= 2 THEN 1
             ELSE 0
        END SectorDetailsRequired ,
        CASE WHEN COUNT(NULLIF(SectorTestRequired, 0)) >= 2 THEN 1
             ELSE 0
        END SectorTestRequired ,
        CASE WHEN COUNT(NULLIF(OtherInfoRequired, 0)) >= 2 THEN 1
             ELSE 0
        END OtherInfoRequired
FROM    #tmp

DROP TABLE #tmp

Outputs:

SectorDetailsRequired   SectorTestRequired  OtherInfoRequired
0                       1                   1

Upvotes: 1

Related Questions