Output key value pairs as JSON from T-SQL query

Your can do a little String Aggregation (assuming not 2017)

Example

Declare @YourTable Table ([ITEMID] varchar(50),[REPORTID] varchar(50),[NAME] varchar(50),[VALUE] varchar(50))  Insert Into @YourTable Values 
 (1,29,'caption','Profit by Product')
,(2,29,'bgColor','#FFFFFF')
,(3,29,'showBorder','0')
,(4,29,'showsum','0')


Select REPORTID
      ,JSONString =  '{' + Stuff((Select concat(',','"',Name,'":"',value,'"') From @YourTable Where ReportID=A.ReportID For XML Path ('')),1,1,'')  + '}'
 From @YourTable A
 Group by REPORTID

Returns

REPORTID    JSONString
29          {"caption":"Profit by Product","bgColor":"#FFFFFF","showBorder":"0","showsum":"0"}

A pivot query along with FOR JSON PATH should generate the JSON output you expect:

SELECT
    MAX(CASE WHEN NAME = 'caption' THEN VALUE END) AS caption,
    MAX(CASE WHEN NAME = 'bgColor' THEN VALUE END) AS bgColor,
    MAX(CASE WHEN NAME = 'showBorder' THEN VALUE END) AS showBorder,
    MAX(CASE WHEN NAME = 'showsum' THEN VALUE END) AS showsum
FROM yourTable
GROUP BY
    ITEMID
FOR JSON PATH;

Demo

Note that if your table contains more than one ITEMID group, then there would be one JSON record in the output per item.

I know this is an old post, but having this question myself I wondered if there was another way. And found one - using the quirky update behaviour of SQL server to perform a custom aggregation using the MODIFY_JSON function in SQL 2016:

go
create or alter function getSourceAsJson(@id int) returns nvarchar(max) as
begin
    declare @j nvarchar(max) = '{}'

    select @j = JSON_MODIFY(@j, '$.' + [name], [value]) 
    from SourceTable
    where Id = @id

    return @j
end
go

select dbo.getSourceAsJson(1) as Json