How to operate this query by puting mentioned condition?

Question

My table is as follows

ID  Name
1   Amit
2   Shalu
2   Ram
3   John
3   Kripa
3   Manish
3   Abhi
1   Ayush

My requirement is to generate a query that will make the format as

ID  Name
1   Amit OR Ayush
2   Shalu OR Ram
3   John AND Kripa AND Manish AND Abhi

Conditions: when count(Id)=2 concatenate OR with Names when count(Id)>3 concatenate AND with Name

I am trying to modify this query to acheive above requirement:

select id,
  REPLACE(stuff((select ',' + '  ' + name  + '  '
from @Table b
where b.id = a.id
  FOR xml path('')),1,1,' '),',','OR') MergedDatafrom @Table agroup by a.id

Answer 1

You could use a union approach:

<query that uses OR>
group by a.id
having count(*) <= 2

UNION ALL

<query that uses AND>
group by a.id
having count(*) > 2