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
How could I find this "the best value to use as the values column type is nvarchar(256)" out dynamically?
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