Case statement not satisfying the condition

Question

Requirment: 1.For class 12 if Gel pen is exists price of it else if ink pen exists ink pen price Inkpen price else if pen exists pen else pen price 2.For class 8 if ink pen is exists price of it else if pen exists pen price else null 3.For class 5 if pen is exists price of it else if pencil exists price of it else null 4.For class 3 if penicl is exists price of it else null

I tried to validate this conditions on SQL SERVER with CASE but return all the rows with matching condition. Any provide solution to validate this in SELECT or using any any other scope for this on ~1Million Data and kind of automating this.

Tables : Student

roll_no class
A   12
B   8
C   3
E   5

stationery

roll_no class obj   price
A       12   pen        10
A       12   pencil     5
A       12   ink pen    14
A       12   gel pen    16
B       8    ink pen    14
C       3    pencil     5
C       3    gel pen    16
E       5    gel pen    16

Result

A   16
B   14
C   5
D   Null

Answer 1

It could use a nested CASE WHEN to calculate a priority for the prices.
And use a window function to get the price with highest priority.
Then aggregate.

select roll_no, min(prio_price) as price
from
(
    select *
    , first_value(case when priority is not null then price end)  
      over (partition by roll_no, class order by 10-priority desc) as prio_price
    from stationary
    cross apply (
    select 
    case class
    when 12 
    then case obj
         when 'gel pen' then 1
         when 'ink pen' then 2
         when 'pen'     then 3
         else 9
         end
    when 8 
    then case obj
         when 'ink pen' then 1
         when 'pen'     then 2
         end
    when 5 
    then case obj
         when 'pen'    then 1
         when 'pencil' then 2
         end
    when 3 
    then case obj
         when 'pencil' then 1
         end
    end as priority
    ) p
) q
group by roll_no
order by roll_no;

roll_no	price
A	16.00
B	14.00
C	5.00
D	null

Demo on db<>fiddle here