out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into?

Question

I sometimes have to unpivot tables, that means, converting all columns into rows.

Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second is the column value. . In the example below I found out manually by looking at the column types in the original table, that the best column to store the value is nvarchar(256).

Questions:

How could I find this "the best value to use as the values column type is nvarchar(256)" out dynamically?

I know I could use sql_variant for the values column type? In this case can I ignore the collate database_default?

That would be an expensive overkill - and I want to avoid that:

sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes.

--=============================================================================
-- 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

Answer 1

Perhaps something like this will suit you:

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 will automatically work out the minimal length and string type of the CONCAT results.

If you need to preserve the underlying type information, including collation, and can live with the more limited set of data types that can be held in sql_variant, use that:

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;