Don't use common denominator, take advantage of your RDBMS special capabilites

I want to justify the use of cross-platform construct:

with p as
(
 select BusinessEntityID, FirstName, MiddleName, LastName, PersonType
 from Person.Person 
)
select p.BusinessEntityId, 'FirstName' as FieldLabel, FirstName
from p
union all
select p.BusinessEntityId, 'MiddleName' as FieldLabel, MiddleName
from p
union all
select p.BusinessEntityId, 'LastName' as FieldLabel, LastName
from p
union all
select p.BusinessEntityId, 'PersonType' as FieldLabel, PersonType
from p
order by BusinessEntityID, FieldLabel;

However, RDBMS-specific constructs are well-optimized:

with p as 
(
select BusinessEntityID, 
    FirstName = convert(varchar, FirstName), 
    LastName = convert(varchar, LastName), 
    MiddleName = ISNULL(convert(varchar,MiddleName), ''), 
    PersonType = convert(varchar, PersonType)
from Person.Person 
)
select unpvt.BusinessEntityID, unpvt.FieldLabel, unpvt.FieldValue
from p
unpivot (FieldValue FOR FieldLabel in (FirstName, MiddleName, LastName, PersonType)) as unpvt
order by unpvt.BusinessEntityID, unpvt.FieldLabel;

These are the two queries' logical reads:

(79888 row(s) affected)
Table 'Person'. Scan count 12, logical reads 5143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(79888 row(s) affected)
Table 'Person'. Scan count 3, logical reads 4186, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.