How to create JSON from an EAV table in SQL Server
Try this one -
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'), (1, 'Gender', 'Male'), (1, 'Key1', 'Value1'), (1, '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 = 1Output -
EntityId JSON----------- --------------------------------------------------------------------------------------------1 { Name: "Saeed", Age: "32", Gender: "Male", Key1: "Value1", Key2: "Value2" }
If you have Sql Server 2016 you can use FOR JSON. You can also use existing CLR libraries such as JsonSelect or Json4Sql.