Sql Syntax to tag rows in a table where this specific criteria appears at least once or more

I am using SQL Server 2014 and I have a table (t1) which contains a list of 6 numbers in more than 25,000 rows.

Extract of t1 is given below:

Id    F1   F2   F3   F4   F5   F6
1     5    11   15   21   30   36
2     8    10   21   25   32   39
3     10   18   23   27   28   32
...

I would like to write 2 Case Statements that would do the following:

• (1) tag the rows where the last digit of any of the 6 numbers (F1 to F6) appear at least once or more as a “Yes”, (else “No”)
• (2) give the count of all numbers (based on condition (1) above), else give a zero

This is what I am expecting as output:

Id    F1   F2   F3   F4   F5   F6  LastDigit   CountLastDigit
1     5    11   15   21   30   36    Yes            4
2     8    10   21   25   32   39    No             0
3     10   18   23   27   28   32    Yes            2
...

I am stuck at writing the logic behind the 2 case statements. Any help would be appreciated.

Answer

A lateral join seems like the right approach. I would do an aggregation as part of the effort:

select t.*, (case when c.num_duplicates > 0 then 'Yes' else 'No' end) as lastDigit,
       coalesce(c.num_duplicates, 0)
from t cross apply
     (select sum(cnt) as num_duplicates
      from (select f % 10 as digit, count(*) as cnt
            from (values (t.f1), (t.f2), (t.f3), (t.f4), (t.f5), (t.f6)) v(f)
            group by f % 10
            having count(*) > 1
           ) c
     ) c;

Here is a db<>fiddle.