-- APPLY can simplify calculations like this;
SELECT 
	p.HPROP, 
	p.STYPE, 
	p.SVALUE,
	x.Newcolumn
FROM PROPOPTIONS P
CROSS APPLY (
	SELECT Newcolumn = CASE 
		WHEN p.STYPE = 'nsffee' THEN 'NSFFee' 
		WHEN p.STYPE = 'MTMFee' THEN 'MTMFee'
		WHEN p.STYPE = 'DDEPOSITINTEREST' THEN 'SecDepIntRate'
		END
) x
WHERE p.hvalue = x.Newcolumn

-- then if you want, you can rewrite like so;
SELECT 
	p.HPROP, 
	p.STYPE, 
	p.SVALUE
FROM PROPOPTIONS P
WHERE p.hvalue = CASE 
		WHEN p.STYPE = 'nsffee' THEN 'NSFFee' 
		WHEN p.STYPE = 'MTMFee' THEN 'MTMFee'
		WHEN p.STYPE = 'DDEPOSITINTEREST' THEN 'SecDepIntRate'
		END

SELECT 
	p.HPROP, 
	p.STYPE, 
	p.SVALUE,
	NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END,
	MTMFee = CASE WHEN p.STYPE = 'MTMFee' THEN p.hvalue END,
	SecDepIntRate = CASE WHEN p.STYPE = 'DDEPOSITINTEREST' THEN p.hvalue END 
FROM PROPOPTIONS P
WHERE p.STYPE IN ('nsffee', 'MTMFee', 'DDEPOSITINTEREST')