取消旋转列时键入强制转换错误，为什么？

Question

我试图在mdsb.sys.database表中设置一些列，但其中一些列遇到了一些问题。在UNPIVOTing表之前，我尝试将所有列都转换为CTE中的NVARCHAR(128)，但它仍然不能工作。有人能解释一下为什么以及如何解决这个问题吗？

;
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

错误：

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.

结果应该是这样的，但是使用"page_verify_option_desc“行和"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

我觉得这其实是个校对问题

试着像这样

  ;
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