By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @myTable table ([Key] nvarchar(max), [value] nvarchar(max))
insert into @myTable ([key],value) values ('Timestamp', convert(varchar(max), getdate(), 120))
insert into @myTable ([key],value) values ('Number', '123456')
SELECT
[Timestamp],
[Number],
-- if you want it as a number, not text, then use:
CAST([Number] AS int) [Number2]
FROM @myTable t
PIVOT (
MAX(value) FOR [key] IN ([Timestamp], [Number])
) pvt
FOR JSON PATH
| JSON_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| [{"Timestamp":"2021-08-27 10:56:33","Number":"123456","Number2":123456}] |