在表的所有列中，如何找到最便宜的一種數據類型，並且可以將所有其他列數據類型轉換為？

我有時必須取消透視表，這意味著將所有列轉換為行。

通常這會變成一個有 2 列的表（只是為了簡化事情）——第一個是列名，第二個是列值。. 在下面的範例中，我通過查看原始表中的列類型手動發現，儲存的最佳列value是nvarchar(256).

問題：

我怎樣才能動態地找到這個*“用作值列類型的最佳值是 nvarchar(256)”*？

我知道我可以將sql_variant用於值列類型？在這種情況下我可以忽略collate database_default嗎？

這將是一個昂貴的矯枉過正- 我想避免這種情況：

sql_variant 的最大長度為 8016 字節。這包括基本類型資訊和基本類型值。實際基本類型值的最大長度為 8,000 字節。

--=============================================================================
-- Unpivot the table - SUCCESSFUL  
--=============================================================================

IF OBJECT_ID('TEMPDB.DBO.#UNPIVOT') IS NOT NULL
  DROP TABLE #UNPIVOT

CREATE TABLE #UNPIVOT(
   [ColumnNames] [nvarchar](128) NULL,
   [AllCol] [nvarchar](256) NULL
) 

INSERT INTO #UNPIVOT([ColumnNames],[AllCol] )
SELECT
    ColumnNames
   ,AllCol
   --INTO TABLEBACKUPS.DBO.UNPIVOT_
FROM (
       select
               [host_platform]                   = CAST (host_platform collate database_default as [nvarchar](256)),
               [host_distribution]               = CAST (host_distribution collate database_default as [nvarchar](256)),
               [host_release]                    = CAST (host_release collate database_default as [nvarchar](256)),
               [host_service_pack_level]         = CAST ([host_service_pack_level] collate database_default as [nvarchar](256)),
               [host_sku]                        = CAST ([host_sku]  as [nvarchar](256)),
               [os_language_version]             = CAST ([os_language_version]  as [nvarchar](256)),
               [host_architecture]               = CAST ([host_architecture] collate database_default as [nvarchar](256))
      from 
      sys.dm_os_host_info 
     ) Radhe
UNPIVOT
(
AllCol
FOR ColumnNames IN ( 
                     host_platform
                    ,host_distribution 
                    ,host_release  
                    ,host_service_pack_level   
                    ,host_sku
                    ,os_language_version   
                   ,host_architecture
                 )
) unpvt


SELECT [ColumnNames]
     ,[AllCol]
FROM #UNPIVOT

也許這樣的事情會適合你：

SELECT
   Unpivoted.*
INTO #Unpivoted
FROM sys.dm_os_host_info AS HI
CROSS APPLY 
(
   VALUES
       (N'host_platform', CONCAT(HI.host_platform, NULL)),
       (N'host_distribution', CONCAT(HI.host_distribution, NULL)),
       (N'host_release', CONCAT(HI.host_release, NULL)),
       (N'host_service_pack_level', CONCAT(HI.host_service_pack_level, NULL)),
       (N'host_sku', CONCAT(HI.host_sku, NULL)),
       (N'os_language_version', CONCAT(HI.os_language_version, NULL)),
       (N'host_architecture', CONCAT(HI.host_architecture, NULL))
) AS Unpivoted
   (ColumnName, TheValue);

SQL Server 將自動計算出結果的最小長度和字元串類型CONCAT。

如果您需要保留基礎類型資訊，包括排序規則，並且可以使用可以保存的更有限的數據類型集sql_variant，請使用：

SELECT
   Unpivoted.*
INTO #Unpivoted
FROM
(
   SELECT
       host_platform = CONVERT(sql_variant, HI.host_platform),
       host_distribution = CONVERT(sql_variant, HI.host_distribution),
       host_release = CONVERT(sql_variant, HI.host_release),
       host_service_pack_level = CONVERT(sql_variant, HI.host_service_pack_level),
       host_sku = CONVERT(sql_variant, HI.host_sku),
       os_language_version = CONVERT(sql_variant, HI.os_language_version),
       host_architecture = CONVERT(sql_variant, HI.host_architecture)
   FROM sys.dm_os_host_info AS HI
) AS HI
UNPIVOT
(
   TheValue FOR ColumnName IN 
   (
       host_platform, 
       host_distribution, 
       host_release, 
       host_service_pack_level, 
       host_sku, 
       os_language_version, 
       host_architecture
   )
) AS Unpivoted;

引用自：https://dba.stackexchange.com/questions/262007