Create json key value from table column name and data

Question

Is it possible to create JSON key value from a table SELECT statement, where column name as key and the column value as value

declare @T table(Id int, ItemName varchar(10), CategoryId int, ItemDate date)
insert into @T
values(1,'ABC',100, '1/1/2020')

to return something as below

{
    "id": 1,
    "table": "tableName",
    "data": [{
            "key": "ItemName",
            "value": "ABC"
        },
        {
            "key": "CategoryId",
            "value": "100"
        },
        {
            "key": "ItemDate",
            "value": "1/1/2020"
        }
    ]
}

I have looked at selecting as JSON but stuck here

select *
from @T 
for json auto

Answer 1

You may try to use VALUES table value constructor and FOR JSON AUTO. As is mentioned in the documentation, when ... you specify the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables.

Table:

CREATE TABLE Tbl (
   Id int, 
   ItemName varchar(10), 
   CategoryId int, 
   ItemDate date
)
INSERT INTO Tbl
VALUES 
   (1, 'ABC', 100, '1/1/2020'),
   (2, 'DEF', 200, '2/2/2020')

Statement:

SELECT t.Id, data.[key], data.[value]
FROM Tbl t
CROSS APPLY (VALUES 
   ('ItemName', CONVERT(varchar(max), ItemName)),
   ('CategoryId', CONVERT(varchar(max), CategoryId)),
   ('ItemDate', CONVERT(varchar(max), ItemDate))
) Data ([key], [value])
FOR JSON AUTO

Result:

[
   {
      "Id":1,
      "Data":[
         {"key":"ItemName", "value":"ABC"},
         {"key":"CategoryId","value":"100"},
         {"key":"ItemDate","value":"2020-01-01"}
      ]
   },
   {
      "Id":2,
      "Data":[
         {"key":"ItemName", "value":"DEF"},
         {"key":"CategoryId", "value":"200"},
         {"key":"ItemDate", "value":"2020-02-02"}
      ]
   }
]

As an additional option you may try to build the inner JSON for each row:

SELECT 
   Id, 
   (
   SELECT [key], [value]
   FROM (VALUES
      ('ItemName', CONVERT(varchar(max), ItemName)),
      ('CategoryId', CONVERT(varchar(max), CategoryId)),
      ('ItemDate', CONVERT(varchar(max), ItemDate))
   ) v ([key], [value])
   FOR JSON PATH
   ) AS Data
FROM Tbl
FOR JSON AUTO