英文:
Why is SQL Server Pivot being case sensitive on TabTypeId instead of treating it as the actual column name?
问题
在T-SQL中，我正在解析JSON并使用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
它没有将tabTypeId视为等于TabTypeId。我得到了NULL值的tabTypeId。
如果我使用TabTypeId，我会得到值9。
为什么会发生这种情况？
英文:
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?
答案1
得分: 6
不是 PIVOT 区分大小写，而是从 OPENJSON 返回的数据区分大小写。如果您检查从中返回的数据，您会发现列 key 使用二进制排序规则：
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 |
对于二进制排序规则，字符的实际 字节 必须匹配。因此，N'tabTypeId' 和 N'TabTypeId' 不相等，因为 N'T' 和 N't' 具有二进制值 0x5400 和 0x7400。
尽管我不确定为什么您要使用 PIVOT；只需在 OPENJSON 调用中定义您的列即可：
SELECT name, --列故意演示不区分大小写
tabTypeId,
type
FROM OPENJSON('{"Name":"tew","TabTypeId":9,"Type":3}')
WITH (Name varchar(3),
TabTypeId int,
Type int);
请注意，在 OPENJSON 的 WITH 子句中，列名仍然区分大小写。tabTypeId int 也会产生 NULL。如果您必须在 SELECT 之前定义一个名为 tabTypeId 的列，您可以使用 tabTypeId int '$.TabTypeId'。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论