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 ',' + QUOTENAME([key], '"') + ':' + QUOTENAME([value], '"')
FROM #temp
)
SELECT output = '{' + STUFF(
(SELECT y FROM x FOR XML PATH, TYPE).value
(N'.[1]', N'nvarchar(max)'), 1, 1, '') + '}';
| output |
|---|
| {"a":"1","b":"2","z":"26"} |