convert rows/table records to json document in sql server

Please note, you can't get with direct query, you have to first PIVOT on storeid.

You can try like following query.

select sid, bid, time,que, hrs,dat from #store_data srcpivot(  MAX([value])  for [key] in (sid, bid, time,que, hrs,dat)) pivfor json auto

Full Example

 if object_id('tempdb.dbo.#store_data') is not null drop table #store_datacreate table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100))INSERT INTO #store_data VALUES ('sid','1','1')INSERT INTO #store_data VALUES ('bid','3','1');INSERT INTO #store_data VALUES ('time','2019-01-01','1');INSERT INTO #store_data VALUES ('que','apple','1');INSERT INTO #store_data VALUES ('sid','2','2');INSERT INTO #store_data VALUES ('bid','5','2');INSERT INTO #store_data VALUES ('hrs','6','2');INSERT INTO #store_data VALUES ('dat','pine','2');select sid, bid, time,que, hrs,dat from #store_data srcpivot(  MAX([value])  for [key] in (sid, bid, time,que, hrs,dat)) pivfor json auto

Output:

[  {    "sid": "1",    "bid": "3",    "time": "2019-01-01",    "que": "apple"  },  {    "sid": "2",    "bid": "5",    "hrs": "6",    "dat": "pine"  }]

Online Demo:

Here

Edit:

PIVOT is automatically ordering on stroeid ASC without specifying any order. In case you want to change it to a different order let say DESC stroeid, in that case you can change the query as following.

select sid, bid, time,que, hrs,dat from #store_data srcpivot(  MAX([value])  for [key] in (sid, bid, time,que, hrs,dat)) pivorder by storeid descfor json auto

Try this:

drop table if exists #store_data;create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100))INSERT INTO #store_data VALUES ('sid','1','1')INSERT INTO #store_data VALUES ('bid','3','1');INSERT INTO #store_data VALUES ('time','2019-01-01','1');INSERT INTO #store_data VALUES ('que','apple','1');INSERT INTO #store_data VALUES ('sid','2','2');INSERT INTO #store_data VALUES ('bid','5','2');INSERT INTO #store_data VALUES ('hrs','6','2');INSERT INTO #store_data VALUES ('dat','pine','2');DECLARE @DynamicTSQLStatement NVARCHAR(MAX)       ,@ColumnNames NVARCHAR(MAX);SELECT @ColumnNames = STUFF(    (        SELECT DISTINCT ',[' + [key] + ']'        FROM #store_data        FOR XML PATH(''), TYPE    ).value('.', 'NVARCHAR(MAX)')   ,1   ,1   ,'');SET @DynamicTSQLStatement = N'select ' + @ColumnNames + 'from #store_dataPIVOT (    MAX([value]) FOR [key] IN (' + @ColumnNames + ')) PVTFOR JSON PATH;';EXEC sp_executesql @DynamicTSQLStatement;

Change to SELECT * in order to get the storeid in the JSON object, too. Also, in SQL Server 2017 you can use STRING_AGG to concatenate the keys.