How to create JSON from an EAV table in SQL Server -

if have simple table like:

id name age   1  saeed 32   2  john  28   3  david 34 

then can create json using for xml in sql server like:

select '{ name : "' + name + '", age : ' + age + ' }' people id = 1 xml path('') 

this easy, because columns known beforehand. however, i'm stuck @ creating json eav table. example, if table is:

id entityid key    value 1  1        name   saeed 2  1        age    32 3  1        gender male 4  1        key1   value1 5  1        key2   value2 

how can create json?

{ name: "saeed", age: 32, gender: "male", key1: "value1", key2: "value2" } 

from query:

select * peopleeav entityid = 1 

please note number of keys variable (it's eav table).

try 1 -

declare @peopleeav table (       id int identity(1,1)     , entityid int     , [key] varchar(30)     , value varchar(100) )  insert @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 + '"'         @peopleeav t2         t2.entityid = t2.entityid         xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '{ ') + ' }'                   (     select distinct entityid     @peopleeav ) t --where entityid = 1 

output -

entityid    json ----------- -------------------------------------------------------------------------------------------- 1           { name: "saeed", age: "32", gender: "male", key1: "value1", key2: "value2" }