It is often said that T-SQL's UNPIVOT does not preserve NULL values. For example the docs have this to say:
Also, null values in the input of UNPIVOT disappear in the output.
What part of the input is being spoken about here? As is well-known, the UNPIVOT clause has three parts. The bit before FOR, the bit before the IN, and the collection enclosed in the brackets.