如果你有一个简单的表,如：
Id Name Age 1 SAEed 32 2 John 28 3 David 34
然后,您可以在sql Server中使用For XML创建JSON,如下所示：
select '{ name : "' + Name + '",age : ' + age + ' }'
from People
where Id = 1
for xml path('')
这很容易,因为预先知道列.但是,我坚持从EAV表创建JSON.例如,如果表是：
Id EntityId Key Value 1 1 Name SAEed 2 1 Age 32 3 1 Gender Male 4 1 Key1 Value1 5 1 Key2 Value2
我该如何创建这个JSON？
{ Name: "SAEed",Age: 32,Gender: "Male",Key1: "Value1",Key2: "Value2" }
从这个查询：
select * from PeopleEav where EntityId = 1
请注意,键的数量是可变的(它是一个EAV表).
解决方法
试试这个 –
DECLARE @PeopleEav TABLE
(
Id INT IDENTITY(1,1),EntityId INT,[Key] VARCHAR(30),Value VARCHAR(100)
)
INSERT INTO @PeopleEav (EntityId,[Key],Value)
VALUES
(1,'Name','SAEed'),(1,'Age','32'),'Gender','Male'),'Key1','Value1'),'Key2','Value2')
SELECT
t.EntityId,JSON = STUFF((
SELECT ',' + [Key] + ': "' + Value + '"'
FROM @PeopleEav t2
WHERE t2.EntityId = t2.EntityId
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'{ ') + ' }'
FROM (
SELECT DISTINCT EntityId
FROM @PeopleEav
) t
--WHERE EntityId = 1
输出 –
EntityId JSON
----------- --------------------------------------------------------------------------------------------
1 { Name: "SAEed",Age: "32",Key2: "Value2" }
