Why Does UNPIVOT Work on Compatibility Level 80 DB?
The answer is in the error message:


  You may need to set the compatibility level of the current database to
  a higher value to enable this feature.


Where 'current database' means the context database - i.e. the database the query is executed in, which is not necessarily the same as the database referenced in the query. For example, the following produces the error message:

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;


The following executes successfully (assuming tempdb has a compatibility level > 80):

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
USE tempdb; -- Context database
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV -- Still references data in AW DB
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;


You need to check what the context database is for the connection that produces the error.