正如Zohar Peled所提到的那样,您需要首先修复您的设计,这很容易通过PARSENAME
完成。
--Create some sample data
CREATE TABLE dbo.YourTable (SomeID int IDENTITY(1,1),[Address] varchar(20),[Type] varchar(20),Setting varchar(100));
GO
INSERT INTO dbo.YourTable (Address,Type,Setting)
VALUES ('All.staff','permission','AllowPost+true'),('All.staff','Allowdelete+False'),'AllowEdit+true'),('manager','AllowPost+False'),'AllowEdit+true');
GO
--Add a new column
ALTER TABLE dbo.YourTable ADD SettingValue bit; --True/false so use a bit
GO
--Split out values
UPDATE dbo.YourTable
SET Setting = PARSENAME(REPLACE(Setting,N'+',N'.'),2),SettingValue = CASE PARSENAME(REPLACE(Setting,1) WHEN 'True' THEN 1 ELSE 0 END;
之后,您可以在子查询中使用FOR JSON
来获得您想要的结果:
SELECT YT.Address,YT.Type,(SELECT J.Setting AS [key],CASE J.SettingValue WHEN 1 THEN 'true' ELSE 'false' END AS [value]
FROM dbo.YourTable J
WHERE J.Address = YT.Address
AND J.Type = YT.Type
FOR JSON PATH) AS setting
FROM dbo.YourTable YT
GROUP BY YT.Address,YT.Type;
,
可以使用纯TSQL来完成此操作,而无需进行任何模式更改,也可以通过几个不同的步骤来使用JSON函数。
步骤1:将setting
转换为JSON格式
首先,需要将setting
“转换”为JSON样式的输出。要确定如何拆分setting
,请使用+
来评估CHARINDEX
的位置:
CHARINDEX('+',setting)
如果返回的值大于0,则可以分割字符串。这是通过SUBSTRING
实现的:
SUBSTRING(setting,CHARINDEX('+',setting))
-- Returns 'AllowPost'
SUBSTRING(setting,setting) + 1,LEN(setting) - CHARINDEX('+',setting) + 1)
-- Returns 'true'
接下来,您可以将字符串与周围的语法连接起来以构建JSON输出:
CONCAT('{ key:',SUBSTRING(setting,setting)),',value: ',setting) + 1),' }')
-- Returns '{ key:AllowPost,value: true }'
最后使用SELECT
语句将其构建到您的CASE
中,以便仅在该列明确包含+
符号时执行字符串操作:
SELECT *,CASE
WHEN CHARINDEX('+',setting) > 0 THEN CONCAT('[{ key:',' }')
ELSE ''
END AS JsonOutput
FROM MyTable
这将输出:
Address Type setting JsonOutput
All.staff permission AllowPost+true { key:AllowPost,value: true }
All.staff permission Allowdelete+False { key:Allowdelete,value: False }
All.staff permission AllowEdit+true { key:AllowEdit,value: true }
manager permission AllowPost+False { key:AllowPost,value: False }
manager permission AllowEdit+true { key:AllowEdit,value: true }
第2步:将结果分组为一行
在SQL Server 2017+中,STRING_AGG
可用于将输出分组为单个行:
STRING_AGG(JsonOutput,')
在SQL语句的上下文中,如下所示:
SELECT Address,CONCAT('[',STRING_AGG(JsonOutput,'),']') AS JsonOutput
FROM (
SELECT *,CASE
WHEN CHARINDEX('+',setting) > 0 THEN CONCAT('{ key:',' }')
ELSE ''
END AS JsonOutput
FROM MyTable
) a
GROUP BY Address,Type
输出:
Address Type JsonOutput
All.staff permission [{ key:AllowPost,value: true },{ key:Allowdelete,value: False },{ key:AllowEdit,value: true }]
manager permission [{ key:AllowPost,value: true }]
本文链接:https://www.f2er.com/3160420.html