我有一个Asset表和Attributes表，其中的属性是简单的键/值对。
DECLARE @Asset TABLE(AssetID INT)
INSERT @Asset VALUES (1)
DECLARE @Att TABLE (AssetID INT, Name NVARCHAR(100), Val NVARCHAR(100))
INSERT @Att VALUES (1, 'height', '100px'), (1, 'width', '200px')
我想编写一个按资产分组并包含具有所有属性的JSON表示形式的列的查询。例如：
AssetID Attributes
------------ -----------------------------------------------
1 {"height":"100px","width":"200px"}
如何编写查询，以便属性名称值成为结果JSON对象中的键？当我使用FOR JSON PATH时，键是列名：
SELECT
AssetID,
(
SELECT Name, Val
FROM @Att att
WHERE att.AssetID = asset.AssetID
FOR JSON PATH
) Attributes
FROM @Asset asset
返回...
AssetID Attributes
------------ -----------------------------------------------
1 [{"Name":"height","Val":"100px"},{"Name":"width","Val":"200px"}]
不确定是否有任何本机JSON方法可以获取列数据，如Key中所述JSON。Alias名称将在中转换为键值JSON。
所以这是我的尝试
您需要旋转数据以获取所需的键值对格式 JSON
如果key是静态的，则
SELECT
AssetID,
(
SELECT Max(CASE WHEN NAME = 'height' THEN Val END) AS height,
Max(CASE WHEN NAME = 'width' THEN Val END) AS width
FROM @Att att
WHERE att.AssetID = asset.AssetID
FOR JSON path, WITHOUT_ARRAY_WRAPPER
) Attributes
FROM @Asset asset
WITHOUT_ARRAY_WRAPPER是默认情况下删除包围JSON该FOR JSON子句输出的方括号
结果：
+---------+--------------------------------------+
| AssetID | Attributes |
+---------+--------------------------------------+
| 1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+
由于密钥可以是任何东西，因此我们需要使用动态查询来透视数据
对于演示，我已将表变量更改为临时表
CREATE TABLE #Asset
(
AssetID INT
)
INSERT #Asset
VALUES (1)
CREATE TABLE #Att
(
AssetID INT,
NAME NVARCHAR(100),
Val NVARCHAR(100)
)
INSERT #Att
VALUES (1,'height','100px'),
(1,'width','200px')
DECLARE @col VARCHAR(8000)= ''
SET @col = (SELECT ',Max(CASE WHEN NAME = ''' + NAME
+ ''' THEN Val END) as ' + Quotename(NAME)
FROM #Att
FOR xml path(''))
SET @col = Stuff(@col, 1, 1, '')
EXEC ('
SELECT
AssetID,
(
SELECT '+@col+'
FROM #Att att
WHERE att.AssetID = asset.AssetID
FOR JSON path, WITHOUT_ARRAY_WRAPPER
) Attributes
FROM #Asset asset')
结果：
+---------+--------------------------------------+
| AssetID | Attributes |
+---------+--------------------------------------+
| 1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句