Need to get the ColumnName2 values when the right columns having > 1 'Y' value from the below sample table.
ColumnName1 ColumnName2 ColumnName3 ColumnName4 ColumnName5 ColumnName6
----------- --------------- ----------- ----------- ----------- -----------
1 ROWNUMBER1 Y NULL NULL NULL
2 ROWNUMBER2 NULL Y NULL NULL
3 ROWNUMBER3 NULL NULL Y NULL
4 ROWNUMBER4 NULL NULL NULL Y
5 ROWNUMBER5 Y Y NULL NULL
6 ROWNUMBER6 NULL Y Y NULL
7 ROWNUMBER7 NULL NULL Y Y
8 ROWNUMBER8 Y NULL NULL Y
9 ROWNUMBER9 Y Y Y NULL
10 ROWNUMBER10 NULL Y Y Y
11 ROWNUMBER11 Y NULL Y Y
12 ROWNUMBER12 Y Y NULL Y
The result/output should be
ColumnName2
---------------
ROWNUMBER5
ROWNUMBER6
ROWNUMBER7
ROWNUMBER8
ROWNUMBER9
ROWNUMBER10
ROWNUMBER11
ROWNUMBER12
The below query will give the expected result. It is not actually a good query and the actual table having 39 rows so the below query won't help. Please help me to get the count based on column in the table.
SELECT ColumnName2
FROM TableName
WHERE ColumnName3 = 'Y' AND (ColumnName4 = 'Y' OR ColumnName5 = 'Y' OR ColumnName6 = 'Y')
OR ColumnName4 = 'Y' AND (ColumnName3 = 'Y' OR ColumnName5 = 'Y' OR ColumnName6 = 'Y')
OR ColumnName5 = 'Y' AND (ColumnName3 = 'Y' OR ColumnName4 = 'Y' OR ColumnName6 = 'Y')
OR ColumnName6 = 'Y' AND (ColumnName3 = 'Y' OR ColumnName4 = 'Y' OR ColumnName5 = 'Y')
One option is to unpivot the columns to rows, then aggregate and filter:
select t.columnname2
from tablename t
cross apply (values (columnname3), (columnname4), (columnname5), (columnname6)) as x(columnname)
group by t.columnname2
having sum(case when x.columnname = 'Y' then 1 else 0 end) > 1