CASE

SELECT
	CASE
		WHEN (ColName IS NULL)
			THEN 1 -- case returns 1
		WHEN ColName = 'Thing'
			THEN 2
		ELSE 3 -- in any other case return 3
	END AS AliasNameForColumn,
	OtherColName
FROM SchemaName.TableName

Multi-layered case

SELECT
	CASE
		WHEN (ColName IS NULL)
			THEN 1 -- case returns 1
		WHEN (ColName IS NOT NULL)
			THEN WHEN ColName = 'A'
							THEN 2
							ELSE 3
	END AS AliasNameForColumn,
	OtherColName
FROM SchemaName.TableName

Using case with a method

SUM(
	CASE 
		WHEN (ColName IS NULL)
			THEN 1
		ELSE 0
	END
)

-- Get the number of all people that do not have a middle name