How to aggregate boolean column
Simply do a GROUP BY
, use MAX()
to return T if available, else F.
select col_1, max(col_2), max(col_3), max(col_4)
from tablename
group by col_1
If COL_2
to COL_4
are text columns (char
, varchar
, varchar2
, nvarchar
, nvarchar2
) containing 'T'
or 'F'
, then you can just take the MAX
of them, since 'T' > 'F'
, i.e. 'T'
comes after 'F'
in lexical order.
SELECT COL_1, MAX(COL_2) AS COL_2, MAX(COL_3) AS COL_3, MAX(COL_4) AS COL_4
FROM table
GROUP BY COL_1
Explanation: The locical operation OR returns TRUE, if at least one of the operands is TRUE. MAX() returns "T"
if at least one value is "T"
and otherwise "F"
.
Note: If the Boolean columns were declared as
COL_x NUMBER(1) DEFAULT 0 NOT NULL
or any other numeric type then I would take MAX(ABS(col_x))
, since a negative value counts as TRUE as well. (If you have an Access frontend with a ComboBox attached to a Boolean, it yields the values 0
or -1
.)
Just as a side note (doesn't work with Oracle): In PostgreSQL, you would do this:
SELECT col_1, bool_or(col_2), bool_or(col_3), bool_or(col_4)
FROM tablename GROUP BY col_1 ORDER BY col_1