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
Best Answer
Perhaps something like this will suit you:
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: