SQL How to read through a grouping of records and output a smaller table based on the values read?

Question

I am trying to read through a group of students records (based on their stateID) and determine if they made the honor roll, or high honors. If they did not reach either, the entire records can be deleted for that grouping of stateID. This is what I came up with as to steps to go through the rows:

1. The code needs to read through each record by like StateID.

2. If it sees an 'N/A' in column C, delete all the records for that StateID. The student did not make the honor roll.

3. If it sees a combination of 'H' and 'HH', output the state ID column, and in the honors column, value of 'H'

4. If it sees all 'HH' values, output the stateID column, and the honors column, value of 'HH'.

Here is an example of the data to be read:

Image of sample data

Image of sample desired output

Answer 1

Here is what I would do. I used Temp table for illustration. I put sample data for demo and got the desired result.

CREATE TABLE #TempTable (StateId varchar(5) ,Honors varchar(5)) 
INSERT INTO #TempTable(StateId, Honors)
values('AA', 'HH'), ('AA', 'N/A'), ('AA', 'N/A'), ('FFB', 'H'),
('FFB', 'HH'), ('BCE', 'N/A'), ('BCE', 'H'), ('AOR', 'H'), ('AOR','H'), ('EEE','N/A'), ('EEE','N/A'), ('YTI','HH'), ('YTI','HH')

-- delete all N/A records
DELETE FrOM #TempTable
where STateId in (select Distinct StateId from #TempTable
where Honors = 'N/A')

-- update HH and H to be H
update #TempTable
set Honors = 'H' 
where StateId in (select Distinct StateId from #TempTable
where Honors = 'H')

select Distinct *  from #TempTable

DROP TABLE #TempTable