The factor preventing us from providing an accurate answer is because we need a set of expected results in a tabular format that the query should generate from the sample data. This is the only robust way to describe the required query logic.

Having said, that I'm including a possible answer using the provided DDL:

-- Set up test data

declare @Student table (

[Student] [varchar](30) NULL,

[IDNo] [varchar](13) NULL,

[Tel3] [varchar](12) NULL,

[Tel1] [varchar](12) NULL,

[Sort] [varchar](12) NULL

);

INSERT INTO @Student

SELECT 'Alison Stew' as Student, '405088' as IDNo, '+77721377200' as Tel3, '+77721377200' as Tel1, null;

INSERT INTO @Student

SELECT 'Alison Stew' as Student, NULL as IDNo, '+77721377200' as Tel3, '+77721377200' as Tel1, null;

INSERT INTO @Student

SELECT 'Amber Egno' as Student, NULL as IDNo, '+7677170700' as Tel3, '+76772476573' as Tel1, null;

INSERT INTO @Student

SELECT 'Amber Egno' as Student, NULL as IDNo, '+76772476573' as Tel3, '+76772476573' as Tel1, null;

INSERT INTO @Student

SELECT 'Carmen Schw' as Student, '083085' as IDNo, '+77711498898' as Tel3, '+77117642110' as Tel1, null;

INSERT INTO @Student

SELECT 'Carmen Schw' as Student, NULL as IDNo, '+77117642110' as Tel3, '+77117642110' as Tel1, null;

-- Query

with rules_per_student as (

selectStudent,

sum(case when IDNo is null then 1 else 0 end) as HasNullRows,

case when min(Tel3) = max(Tel3) then 1 else 0 end as IdenticalTel3Rows,

case when min(Tel1) = max(Tel1) then 1 else 0 end as IdenticalTel1Rows,

sum(case when Tel1 = Tel3 then 1 else 0 end) as HasTel1EqualTel3InRow

from @Student

group by Student

)

selects.Student,

s.IDNo,

s.Tel3,

s.Tel1,

case when r.HasNullRows > 0 then

case when IdenticalTel3Rows = 1 then '1111'

when IdenticalTel3Rows = 0 and IdenticalTel1Rows = 1 then '8888'

when IdenticalTel3Rows = 0 and IdenticalTel1Rows = 0 and HasTel1EqualTel3InRow > 0 then '7777'

end

else null end as Sort

from @Student s

join rules_per_student r on r.student = s.student