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.

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;

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.

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.