Reputation: 566
SQL tableA have 4 rows of Data, with 3 columns.
Expected Result is :
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
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
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