The type of column Weight conflicts with the type of other columns specified in the UNPIVOT list.

DECLARE @PID INT = 777
SELECT
 [Параметр],
 [Значение]
FROM
(
 SELECT 
  Name,
  CAST(Size AS NVARCHAR(50)) AS Size,
  CAST(Weight AS NVARCHAR(50)) AS Weight,
  CAST(Color AS NVARCHAR(50)) AS Color
 FROM AdventureWorks2008R2.Production.Product
 WHERE ProductID = @PID
) AS Src

UNPIVOT
(
 [Значение] FOR [Параметр] IN (Name,Size,Weight,Color)
) AS UnPvt

Возвращает:

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

SOLUTION:

Получаем Collation для данных столбцов таблицы

USE master;
SELECT DB_NAME() AS Database_Name,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DB_Collation,
       SQL_VARIANT_PROPERTY(Name, 'Collation') AS Name_Collation,
       SQL_VARIANT_PROPERTY(CAST(Size AS NVARCHAR(50)), 'Collation') AS Size_Collation,
       SQL_VARIANT_PROPERTY(CAST(Weight AS NVARCHAR(50)), 'Collation') AS Weight_Collation,
       SQL_VARIANT_PROPERTY(CAST(Color AS NVARCHAR(50)), 'Collation') AS Color_Collation
FROM AdventureWorks2008R2.Production.Product
WHERE ProductID = 777;

Видим:

Database_Name  DB_Collation     Name_Collation                Size_Collation                Weight_Collation  Color_Collation
-------------- ---------------- ----------------------------- ----------------------------- ----------------- -----------------------------
master         Ukrainian_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  Ukrainian_CI_AS   SQL_Latin1_General_CP1_CI_AS

(1 row(s) affected)

При этом:

USE AdventureWorks2008R2;
SELECT DB_NAME() AS Database_Name,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DB_Collation,
       SQL_VARIANT_PROPERTY(Name, 'Collation') AS Name_Collation,
       SQL_VARIANT_PROPERTY(CAST(Size AS NVARCHAR(50)), 'Collation') AS Size_Collation,
       SQL_VARIANT_PROPERTY(CAST(Weight AS NVARCHAR(50)), 'Collation') AS Weight_Collation,
       SQL_VARIANT_PROPERTY(CAST(Color AS NVARCHAR(50)), 'Collation') AS Color_Collation
FROM AdventureWorks2008R2.Production.Product
WHERE ProductID = 777;

Видим:

Database_Name         DB_Collation                  Name_Collation                Size_Collation                Weight_Collation              Color_Collation
--------------------- ----------------------------- ----------------------------- ----------------------------- ----------------------------- ------------------------------
AdventureWorks2008R2  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS  SQL_Latin1_General_CP1_CI_AS

(1 row(s) affected)

• Если выполняем запрос с уровня master, то указываем COLLATE

  DECLARE @PID INT = 777
  SELECT
   [Параметр],
   [Значение]
  FROM
  (
   SELECT 
    Name,
    CAST(Size AS NVARCHAR(50)) AS Size,
    CAST(Weight AS NVARCHAR(50)) COLLATE SQL_Latin1_General_CP1_CI_AS AS Weight,
    CAST(Color AS NVARCHAR(50)) AS Color
   FROM AdventureWorks2008R2.Production.Product
   WHERE ProductID = @PID
  ) AS Src
  
  UNPIVOT
  (
   [Значение] FOR [Параметр] IN (Name,Size,Weight,Color)
  ) AS UnPvt

  Вывод:

  Параметр  Значение
  --------- -----------------------
  Name      Mountain-100 Black, 44
  Size      44
  Weight    21.13
  Color     Black
  
  (4 row(s) affected)

• Если выполним с уровня AdventureWorks2008R2, то проблема уйдет

  USE AdventureWorks2008R2 
  DECLARE @PID INT = 777
  SELECT
   [Параметр],
   [Значение]
  FROM
  (
   --DECLARE @PID INT = 777
   SELECT 
    Name,
    CAST(Size AS NVARCHAR(50)) AS Size,
    CAST(Weight AS NVARCHAR(50)) AS Weight,
    CAST(Color AS NVARCHAR(50)) AS Color
   FROM AdventureWorks2008R2.Production.Product
   WHERE ProductID = @PID
  ) AS Src
  
  UNPIVOT
  (
   [Значение] FOR [Параметр] IN (Name,Size,Weight,Color)
  ) AS UnPvt

  Вывод тотже

  Параметр  Значение
  --------- -----------------------
  Name      Mountain-100 Black, 44
  Size      44
  Weight    21.13
  Color     Black
  
  (4 row(s) affected)

FROM

SELECT * FROM full_server.db.table

SELECT 
    * 
FROM SQL-PC.AdventureWorks2008R2.Sales.Currency

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '-'.

SELECT @@SERVERNAME

------------------------------------------------
SQL-PC

(1 row(s) affected)


SOLUTION

SELECT 
    * 
FROM [SQL-PC].AdventureWorks2008R2.Sales.Currency