Unpivot sys.assembly_modules System View with Collation Issue

The system view sys.assembly_modules has this structure:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-assembly-modules-transact-sql?view=sql-server-2017

Column_name
object_id
assembly_id
assembly_class
assembly_method
null_on_null_input
execute_as_principal_id

We can check this view with sp_help:
USE master;
GO
EXEC sp_help ‘sys.assembly_modules’;
GO

/*
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
object_id int no 4 10 0 no (n/a) (n/a) NULL
assembly_id int no 4 10 0 no (n/a) (n/a) NULL
assembly_class nvarchar no 256 yes (n/a) (n/a) Latin1_General_BIN
assembly_method nvarchar no 256 yes (n/a) (n/a) Latin1_General_BIN
null_on_null_input bit no 1 yes (n/a) (n/a) NULL
execute_as_principal_id int no 4 10 0 yes (n/a) (n/a) NULL
*/
Both assembly_class and assembly_method have collation of Latin1_General_BIN, which is not a default collation for most servers I have seen.

The following unpivot code will generate error:

SELECT [Label],[Value]
FROM
(
SELECT
CAST([assembly_id] as sysname) AS [assembly_id],
[assembly_method]
FROM
[sys].[assembly_modules]
) AS [t]
UNPIVOT
(
[Value] FOR [Label] IN
(
[assembly_id],
[assembly_method]
)
) AS [v];

Msg 8167, Level 16, State 1, Line 19
The type of column “assembly_method” conflicts with the type of other columns specified in the UNPIVOT list.

But you can add the Latin1_General_BIN collation to the cast assembly_id column.

SELECT [Label],[Value]
FROM
(
SELECT
CAST([assembly_id] as sysname) Collate Latin1_General_BIN AS [assembly_id],
[assembly_method]
FROM
[sys].[assembly_modules]
) AS [t]
UNPIVOT
(
[Value] FOR [Label] IN
(
[assembly_id],
[assembly_method]
)
) AS [v];

But another unpivot syntax (CROSS APPLY )can avoid this error without explicit adding collation:

select Label,Value from (SELECT
Cast([assembly_id] as nvarchar(128)) [assembly_id],
[assembly_method]
FROM [master].[sys].[assembly_modules]) t
Cross apply (Values(‘assembly_id’ ,[assembly_id])
,(‘assembly_method’, [assembly_method]) ) d(Label,Value)

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e3745569-45f3-4f0d-8f23-cbbbc2030fec/unpivot-data-type-error?forum=transactsql

–******************* Convert_Implict in play for the result Collation

Declare @Sample Table(
ColA int
–varchar(10) Collate SQL_Latin1_General_CP1_CI_AS
, ColB varchar(10) Collate Latin1_General_Bin);
Insert @Sample(ColA, ColB) Values (‘1’, ‘x’);
Insert @Sample(ColA, ColB) Values (‘2’, ‘y’);

select Label,Value from (

SELECT
Cast (ColA as varchar(10) ) as ColA, ColB
FROM @Sample) t
Cross apply (Values(‘ColA’ ,[ColA])
,(‘ColB’, [ColB]) ) d(Label,Value);
I found some related information to this issue.

I checked the execution plan of this query, there is an implicit_convert for colA to varchar(10).

Based on Collation Precedence document

https://docs.microsoft.com/en-us/sql/t-sql/statements/collation-precedence-transact-sql?view=sql-server-2017

, the result column (value column after unpivot ) collation should be Latin1_General_Bin in this sample.

I put the result to another table and check the column collation of the column value and it is confirmed that this is the case.
The following table summarizes the rules.

Operand coercion label Explicit X Implicit X Coercible-default No-collation
Explicit Y Generates Error Result is Explicit Y Result is Explicit Y Result is Explicit Y
Implicit Y Result is Explicit X Result is No-collation Result is Implicit Y Result is No-collation
Coercible-default Result is Explicit X Result is Implicit X Result is Coercible-default Result is No-collation
No-collation Result is Explicit X Result is No-collation Result is No-collation Result is No-collation

Dynamic Pivot with CASE for Dates

  
 
 create table sick_codes_Test$ (
 EMP_ID int, EMP_SHORT_NAME varchar(100), SEG_CODE varchar(10), 
 NOM_DATE date)
 Insert into sick_codes_Test$ values 
 (1,'aaa','s','2018-07-17'),
  (1,'aaa','s','2018-07-18'),
   (1,'aaa','s','2018-07-19'),
    (1,'aaa','s','2018-07-20'),
	 (1,'aaa','s','2018-07-21'),
	  (1,'aaa','s','2018-07-22'),
	   (1,'aaa','s','2018-07-23')
	   ,(2,'bbb','s2','2018-07-22'),
	   (2,'bbb','s2','2018-07-23')
declare @ColumnHeaders NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7

-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, getdate() ) dt  
from(Select n from nums) D(n)
)
  
Select @ColumnHeaders = STUFF( (SELECT ',' + 'max (Case when NOM_DATE='+ quotename( Convert(char(8),dt,112),'''') +' then  SEG_CODE  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
FROM  dates
Order by n
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  
 --print @ColumnHeaders

	  SET @sql = N'Select EMP_ID, EMP_SHORT_NAME, '
+ @ColumnHeaders 
+' from sick_codes_Test$
Group by EMP_ID, EMP_SHORT_NAME ' 

--print @sql
 EXEC sp_executesql @sql;
  

  drop table sick_codes_Test$


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a77fecf-75a7-43ec-90fa-2b33888633c2/how-to-use-dynamic-dates-in-sql-pivot?forum=transactsql