Search code examples
sqlsql-servert-sqlpivot-table

Why is SQL Server Pivot being case sensitive on TabTypeId instead of treating it as the actual column name?

In T-Sql I am parsing JSON and using PIVOT.

Select * from (select [key],convert(varchar,[value])[value] 
from openjson ('{"Name":"tew","TabTypeId":9,"Type":3}'))A
    pivot(max(value) for [key] in ([Name],tabTypeId,[Type]))b

It is not treating tabTypeId as equal to TabTypeId. I am getting NULL for tabTypeId.
If I use TabTypeId I get the value 9.
Why is it happening?

Solution

  • It's not PIVOT that is case sensitive, it's the data returned from OPENJSON that is. If you check the data returned from it, you'll see that the column key is a binary collation:

    SELECT name, system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT [key], CONVERT(varchar, [value]) AS [value] FROM OPENJSON(''{"Name":"tew","TabTypeId":9,"Type":3}'');',NULL,NULL)
    name system_type_name collation_name
    key nvarchar(4000) Latin1_General_BIN2
    value varchar(30) SQL_Latin1_General_CP1_CI_AS

    For binary collations the actual bytes of the characters must match. As such N'tabTypeId' and N'TabTypeId' are not equal as N'T' and N't' have the binary values 0x5400 and 0x7400.

    Though I am unsure why you are using PIVOT at all; just define your columns in your OPENJSON call:

    SELECT name, --Columns are intentionally demonstrating non-case sensitivity
       tabTypeId,
       type
FROM OPENJSON('{"Name":"tew","TabTypeId":9,"Type":3}')
        WITH (Name varchar(3),
              TabTypeId int,
              Type int);

    Note that in the WITH clause of OPENJSON the column names are still case sensitive. tabTypeId int would also yield NULL. If you "had" to have a column called tabTypeId defined prior to the SELECT you would use tabTypeId int '$.TabTypeId' instead.