How can I query a table with key/value pairs into a JSON object using FOR JSON PATH?

I have an Asset table and an Attributes table, where the attributes are simple key/value pairs.

DECLARE @Asset TABLE(AssetID INT)
INSERT @Asset VALUES (1)

DECLARE @Att TABLE (AssetID INT, Name NVARCHAR(100), Val NVARCHAR(100))
INSERT @Att VALUES (1, 'height', '100px'), (1, 'width', '200px')

I would like to write a query that groups by Asset and contains a column with the JSON representation of all attributes. For example:

AssetID      Attributes
------------ -----------------------------------------------
1            {"height":"100px","width":"200px"}

How can I write the query so that the attribute name value becomes the key in the resulting JSON object? When I use FOR JSON PATH, the keys are the column names:

SELECT
    AssetID,
    (
        SELECT Name, Val
        FROM @Att att
        WHERE att.AssetID = asset.AssetID
        FOR JSON PATH
    ) Attributes
FROM @Asset asset

which returns...

AssetID      Attributes
------------ -----------------------------------------------
1            [{"Name":"height","Val":"100px"},{"Name":"width","Val":"200px"}]

Am not sure about any native JSON methods to get the column data as Key in JSON. Alias names will be converted to key value in JSON.

So here is my try

You need to pivot the data to get the required key value pair format in JSON

If the key is static then

SELECT
    AssetID,
    (
        SELECT Max(CASE WHEN NAME = 'height' THEN Val END) AS height,
               Max(CASE WHEN NAME = 'width' THEN Val END) AS width
        FROM @Att att
        WHERE att.AssetID = asset.AssetID
        FOR JSON path, WITHOUT_ARRAY_WRAPPER
    ) Attributes
FROM @Asset asset

WITHOUT_ARRAY_WRAPPER is to remove the square brackets that surround the JSON output of the FOR JSON clause by default

Result:

+---------+--------------------------------------+
| AssetID |              Attributes              |
+---------+--------------------------------------+
|       1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+

Since the key can be anything we need to use dynamic query to pivot the data

For demo I have changed the table variable to temp table

CREATE TABLE #Asset
  (
     AssetID INT
  )

INSERT #Asset
VALUES (1)

CREATE TABLE #Att
  (
     AssetID INT,
     NAME    NVARCHAR(100),
     Val     NVARCHAR(100)
  )

INSERT #Att
VALUES (1,'height','100px'),
       (1,'width','200px')

DECLARE @col VARCHAR(8000)= ''

SET @col = (SELECT ',Max(CASE WHEN NAME = ''' + NAME
                   + ''' THEN Val END) as ' + Quotename(NAME)
            FROM   #Att
            FOR xml path(''))
SET @col = Stuff(@col, 1, 1, '')

EXEC ('
SELECT
    AssetID,
    (
        SELECT '+@col+'
        FROM #Att att
        WHERE att.AssetID = asset.AssetID
        FOR JSON path, WITHOUT_ARRAY_WRAPPER
    ) Attributes
FROM #Asset asset') 

Result:

+---------+--------------------------------------+
| AssetID |              Attributes              |
+---------+--------------------------------------+
|       1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+