CASE Statement on multiple columns values SQL in SELECT Query -SQL

 if you want to change two columns values

select *, PERCENTAGE_OF_LOCAL_SEAT = CASE 

WHEN LEVEL_NAME = 'L1-L2' THEN 0

WHEN LEVEL_NAME = 'L3' THEN 20 

WHEN LEVEL_NAME = 'L4' THEN 70 

WHEN LEVEL_NAME = 'L5' THEN 10

ELSE 0 END

from TableName

or with union all, all before and after union all columns number must be same

select LEVEL_ID, LEVEL_NAME, TOTAL_GRANT_SEAT, PERCENTAGE_OF_TOTAL_SEAT = CASE 

WHEN LEVEL_NAME = 'L1-L2' THEN 0

WHEN LEVEL_NAME = 'L3' THEN 20 

WHEN LEVEL_NAME = 'L4' THEN 70 

WHEN LEVEL_NAME = 'L5' THEN 0

WHEN LEVEL_NAME = 'L5/L6' THEN 10

ELSE 0 END, TOTAL_CALCULATED_SEAT, TOTAL_SEAT_LEFT, TOTAL_SEAT_ALLOTED, 

TOTAL_CALCULATED_SEAT_60, TOTAL_SEAT_LEFT_60, TOTAL_SEAT_ALLOTED_60,

TOTAL_CALCULATED_SEAT_40, TOTAL_SEAT_LEFT_40, TOTAL_SEAT_ALLOTED_40

from @temp

Union All

select  

0,

'Total',

SUM(ISNULL(TOTAL_GRANT_SEAT,0)),

SUM(ISNULL(PERCENTAGE_OF_TOTAL_SEAT,0)),

SUM(ISNULL(TOTAL_CALCULATED_SEAT,0)),

SUM(ISNULL(TOTAL_SEAT_LEFT,0)),

SUM(ISNULL(TOTAL_SEAT_ALLOTED,0)),

SUM(ISNULL(TOTAL_CALCULATED_SEAT_60,0)),

SUM(ISNULL(TOTAL_SEAT_LEFT_60,0)),

SUM(ISNULL(TOTAL_SEAT_ALLOTED_60,0)),

SUM(ISNULL(TOTAL_CALCULATED_SEAT_40,0)),

SUM(ISNULL(TOTAL_SEAT_LEFT_40,0)),

SUM(ISNULL(TOTAL_SEAT_ALLOTED_40,0))

from @temp