sql One-many and one-one relation between columns

Question

I have no idea if the title matches what I am trying to achieve in the query given below. The example might not be great but it serves what I am trying to achieve. There are three results that I am trying to get as shown below. Can this be done with a single query showing all the results in a better way?

TABLE:

name    surname gender
arjun   khadka  female
arjun   khadka  male
arjun   basnet  male
kumar   khadka  female
kumar   basnet  female
arjun   khadka  female
arjun   basnet  female
kumar   khadka  female

query:

    WITH cte
    AS (SELECT
      *,
      DENSE_RANK() OVER (ORDER BY name, surname) AS groupid1,
      DENSE_RANK() OVER (ORDER BY name, surname, gender) AS groupid2
    FROM (SELECT
      name,
      surname,
      gender

   FROM name) x)

1. same person with duplicate gender as well as with non duplicate gender

     SELECT
     name,
     surname,
     gender
   FROM CTE
   WHERE groupid1 IN (SELECT
     groupid1
   FROM cte
   GROUP BY groupid1,
            groupid2
   HAVING COUNT(*) > 1)
   AND groupid1 IN (SELECT
     groupid1
   FROM cte
   GROUP BY groupid1,
            groupid2
   HAVING COUNT(*) = 1)
   

2. same person with no duplicate gender at all

   SELECT
     *
   FROM cte
   WHERE groupid1 NOT IN (SELECT
     groupid1
   FROM cte
   GROUP BY groupid1,
            groupid2
   HAVING COUNT(*) > 1)
   

3. same person having duplicate gender record only

   SELECT
     *
   FROM cte
   WHERE groupid1 IN (SELECT
     groupid1
   FROM cte
   GROUP BY groupid1,
            groupid2
   HAVING COUNT(*) > 1)
   AND groupid1 NOT IN (SELECT
     groupid1
   FROM cte
   GROUP BY groupid1,
            groupid2
   HAVING COUNT(*) = 1)
   

I modified what @uzi has provided, as solution as it has some flaws:

        select
           distinct  name, surname,personCount as count
            , personHasDupNUniqueGender = iif(min(allRowsCount) over (partition by personGroupId) = 1 and max (allRowsCount) over (partition by personGroupId) >1,1,0)
            , personHasUniqueGender = iif(max(allRowsCount) over (partition by personGroupId) = 1,1,0)
            , personHasAllDupGender = iif(personCount = allrowscount and personcount>1,1,0)
        from (
            select 
               *
                , personGroupId= DENSE_RANK() over (order by name, surname)
                , personCount = count(*) over (partition by name, surname)
                 , allRowsCount = count(*) over (partition by name, surname, gender)
            from 
                name
        ) t

        order by name, surname

Answer 1

Here's one way. Query returns all rows with extra columns condition_1, condition_2, condition_3. Values of those columns either 1 or 0. 1 - matches condition

declare @t table (
    name varchar(100)
    , surname varchar(100)
    , gender varchar(100)
)
insert into @t values 
('arjun', 'khadka', 'female')
,('arjun', 'khadka', 'male')
,('arjun', 'basnet', 'male')
,('kumar', 'khadka', 'female')
,('kumar', 'basnet', 'female')
,('arjun', 'khadka', 'female')
,('arjun', 'basnet', 'female')
,('kumar', 'khadka', 'female')

select
    name, surname, gender
    , condition_1 = max(iif(cnt_1 = 1 and cnt_2 > 2, 1, 0)) over (partition by name, surname)
    , condition_2 = iif(cnt_2 - cnt_1 <= 1 and cnt_1 = 1, 1, 0)
    , condition_3 = iif(cnt_2 = cnt_1 and cnt_1 > 1, 1, 0)
from (
    select 
        *, cnt_1 = count(*) over (partition by name, surname, gender)
        , cnt_2 = count(*) over (partition by name, surname)
    from 
        @t
) t
order by name, surname

Output

name    surname   gender   condition_1   condition_2   condition_3
-------------------------------------------------------------------
arjun   basnet    female    0            1             0
arjun   basnet    male      0            1             0
arjun   khadka    female    1            0             0
arjun   khadka    female    1            0             0
arjun   khadka    male      1            0             0
kumar   basnet    female    0            1             0
kumar   khadka    female    0            0             1
kumar   khadka    female    0            0             1

Edit:

select
    name, surname, gender, cnt_1, cnt_2
    , condition_1 = max(iif(cnt_1 = 1 and cnt_2 > 2, 1, 0)) over (partition by name, surname)
    , condition_2 = iif(max(cnt_1) over (partition by name, surname) = 1, 1, 0)
    , condition_3 = iif(cnt_2 = cnt_1 and cnt_1 > 1, 1, 0)
from (
    select 
        *, cnt_1 = count(*) over (partition by name, surname, gender)
        , cnt_2 = count(*) over (partition by name, surname)
    from 
        @t
) t
order by name, surname