By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #temp
(
[key] nvarchar(30), -- always specify a length!
[value] nvarchar(2)
);
insert #temp ([key], [value]) values
('a', '1'),
('b', '2'),
('z', '26');
;WITH x(y) AS
(
SELECT '"' + STRING_ESCAPE([key], 'json') + '":"'
+ STRING_ESCAPE([value], 'json') + '"'
FROM #temp
)
SELECT output = '{' + STRING_AGG(y, ',') + '}' FROM x;
| output |
|---|
| {"a":"1","b":"2","z":"26"} |