How can one select a static value as a column based when using UNPIVOT?
You are missing a new column generated by unpivoting attributes 

SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue]
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT 
(
  Value FOR attributes IN ( type, code, description)
) AS myValues