SQL Server - Generate Dynamic JSon output

Solution 1:

I don't think you can build the required JSON directly (usinf FOR JSON), but you may try to build one part of the JSON output using basic string concatenation and aggregation. Note, that starting for SQL Server 2016, you need to use FOR XML PATH for aggregation:

Data:

SELECT *
INTO HeaderTable
FROM (VALUES
   (1, 'User Name Change')
) v (RequestID, RequestType)

SELECT *
INTO ValuesTable
FROM (VALUES
   (1, 'MobileNo', '07777777777'),
   (1, 'Name',  'Fred Bloggs'),
   (1, 'Address',  'Full address'),
   (2, 'MobileNo', '07888888888'),
   (2, 'Name', 'John Smith')
) v (RequestID, Alias, FieldValue)

Statement for SQL Server 2017:

SELECT 
   hdr.RequestType AS request_type, 
   JSON_QUERY((
      SELECT CONCAT(
         '[{',
         STRING_AGG(
            CONCAT(
               '"', 
               STRING_ESCAPE(vals.Alias, 'json'), 
               '":"', 
               STRING_ESCAPE(vals.FieldValue, 'json'), '"'
            ), 
            ','
         ),
         '}]'
      )   
      FROM ValuesTable vals
      WHERE vals.RequestID = hdr.[RequestID]
   )) AS request_details
FROM HeaderTable hdr
FOR JSON PATH

Statement for SQL Server 2016:

SELECT 
   hdr.RequestType AS request_type, 
   JSON_QUERY(CONCAT(
      '[{',
      STUFF(
         (
         SELECT CONCAT(',"', vals.Alias, '":"', vals.FieldValue, '"') 
         FROM ValuesTable vals
         WHERE vals.RequestID = hdr.[RequestID]
         FOR XML PATH(''), TYPE
         ).value('.', 'varchar(max)'),
         1, 1, ''
      ),
      '}]'
   )) AS request_details
FROM HeaderTable hdr
FOR JSON PATH

Result:

[
   {
      "request_type":"User Name Change",
      "request_details":[
         {
            "MobileNo":"07777777777",
            "Name":"Fred Bloggs",
            "Address":"Full address"
         }
      ]
   }
]