I'm trying to write a query which aggregates many rows and returns a single string value to indicate whether or not each column contains a value. It needs to examine each column and if the column contains a 'true' value, then concatenate the string result to indicate so.
Given (on SQL Server 2008):
Col1 | Col2 Row1: 0 0 Row2: 0 1
I need a result stating "Col1 has no true, Col2 has true" (the comma doesn't matter).
My assumption is that I need to combine a CASE
or IF
statement with an ANY
operator, but so far the syntax escapes me.
The following query will produce these results: Col1 has no true, Col2 has true
for your data:
SELECT CASE
WHEN Col1Total = 0 THEN 'Col1 has no true'
ELSE 'Col1 has true'
END + ', ' +
CASE
WHEN Col2Total = 0 THEN 'Col2 has no true'
ELSE 'col2 has true'
END AS yourResult
FROM
(
SELECT SUM(Col1) AS Col1Total, SUM(Col2) AS Col2Total
FROM yourTable
) AS t