Type cast error when unpivoting columns, why?

Question

Im trying to UNPIVOT some columns in the mdsb.sys.database table but having some trouble with some of the columns. I tried to CAST all columns to NVARCHAR(128) in a CTE before UNPIVOTing the table but it still doesnt work. Can someone explain why and how to solve the problem?

;
WITH props AS (
SELECT 
    [name]
    ,CAST([collation_name] AS NVARCHAR(128)) AS [collation_name]
    ,CAST([is_auto_close_on] AS NVARCHAR(128)) AS [is_auto_close_on]
    ,CAST([is_auto_shrink_on] AS NVARCHAR(128)) AS [is_auto_shrink_on]
    ,CAST([is_auto_create_stats_on] AS NVARCHAR(128)) AS [is_auto_create_stats_on]
    ,CAST([is_auto_update_stats_async_on] AS NVARCHAR(128)) AS [is_auto_update_stats_async_on]
    ,CAST([is_auto_update_stats_on] AS NVARCHAR(128)) AS [is_auto_update_stats_on]
    ,CAST(CASE WHEN [is_parameterization_forced] = 0 THEN N'SIMPLE' ELSE N'FORCED' END AS NVARCHAR(128)) AS [is_parameterization_forced]
    ,CAST([is_trustworthy_on] AS NVARCHAR(128)) AS [is_trustworthy_on]
    ,CAST([compatibility_level] AS NVARCHAR(128)) AS [compatibility_level]
    ,CAST([page_verify_option_desc] AS NVARCHAR(128)) AS [page_verify_option_desc]
FROM sys.databases s
WHERE [name] = DB_NAME()
)
SELECT [name]
        ,[property]
        ,[value] 
FROM [props]
UNPIVOT
(
    [value]
    FOR [property] IN ([collation_name] 
                        ,[is_auto_close_on]
                        ,[is_auto_shrink_on]
                        ,[is_auto_create_stats_on]
                        ,[is_auto_update_stats_async_on]
                        ,[is_auto_update_stats_on]
                        ,[is_parameterization_forced]
                        ,[is_trustworthy_on]
                        ,[compatibility_level]
                        ,[page_verify_option_desc]
                        )
) AS u

Error:

Msg 8167, Level 16, State 1, Line 34
The type of column "page_verify_option_desc" conflicts with the type of other columns specified in the UNPIVOT list.

The result should look something like this but with the "page_verify_option_desc" row and "recovery_model_desc".

name    property    value
master  collation_name  SQL_Latin1_General_CP1_CI_AS
master  is_auto_close_on    0
master  is_auto_shrink_on   0
master  is_auto_create_stats_on 1
master  is_auto_update_stats_async_on   0
master  is_auto_update_stats_on 1
master  is_parameterization_forced  SIMPLE
master  is_trustworthy_on   0
master  compatibility_level 110

Answer 1

I think It's actually a collation issue

Try like this

  ;
WITH props AS (
SELECT 
    [name]
    ,CAST([collation_name] AS NVARCHAR(128)) AS [collation_name]
    ,CAST([is_auto_close_on] AS NVARCHAR(128)) AS [is_auto_close_on]
    ,CAST([is_auto_shrink_on] AS NVARCHAR(128)) AS [is_auto_shrink_on]
    ,CAST([is_auto_create_stats_on] AS NVARCHAR(128)) AS [is_auto_create_stats_on]
    ,CAST([is_auto_update_stats_async_on] AS NVARCHAR(128)) AS [is_auto_update_stats_async_on]
    ,CAST([is_auto_update_stats_on] AS NVARCHAR(128)) AS [is_auto_update_stats_on]
    ,CAST(CASE WHEN [is_parameterization_forced] = 0 THEN N'SIMPLE' ELSE N'FORCED' END AS NVARCHAR(128)) AS [is_parameterization_forced]
    ,CAST([is_trustworthy_on] AS NVARCHAR(128)) AS [is_trustworthy_on]
    ,CAST([compatibility_level] AS NVARCHAR(128)) AS [compatibility_level]
    ,CAST([page_verify_option_desc] collate database_default AS NVARCHAR(128)) AS [page_verify_option_desc]
FROM sys.databases s
WHERE [name] = DB_NAME()
)
SELECT [name]
        ,[property]
        ,[value] 
FROM [props]
UNPIVOT
(
    [value]
    FOR [property] IN ([collation_name] 
                        ,[is_auto_close_on]
                        ,[is_auto_shrink_on]
                        ,[is_auto_create_stats_on]
                        ,[is_auto_update_stats_async_on]
                        ,[is_auto_update_stats_on]
                        ,[is_parameterization_forced]
                        ,[is_trustworthy_on]
                        ,[compatibility_level]
                        ,[page_verify_option_desc]
                        )
) AS u