title: How to structure JSON SQL by row using values from the row?
body: <p>I'm struggling to structure JSON using SQL.</p>
  
  <p>Say I have a table like this:</p>
  
  <pre><code>| col1          | col2     | col3     |
  +---------------+----------+----------+
  | specialvalue  | someval  | someval  |
  | specialvalue2 | someval2 | someval2 |
  |               |          |          |
  </code></pre>
  
  <p>I'm trying to get a structure like the following:</p>
  
  <pre><code>{
      "specialvalue": {
          "specialcol": "specialvalue",
          "col2": "someval",
          "col3": "someval"
      },
      "specialvalue2": {
          "specialcol": "specialvalue2",
          "col2": "someval2",
          "col3": "someval2"
      }
  }
  </code></pre>
  
  <p>How can I accomplish this? Can I use JSON_MODIFY with dynamic keys, while mapping through every row in the set?</p>
  
  <p>The closest I've gotten is the following:</p>
  
  <pre><code>SELECT
       specialcol,
       col2,
       col3
  INTO #tmpTbl
  FROM myTable
  
  SELECT 
      specialcol, 
      (SELECT * FROM #tmpTbl FOR JSON AUTO) as 'Value' 
  FROM #tmpTbl 
  FOR JSON AUTO
  
  DROP TABLE #tmp
  </code></pre>
  
  <p>Which returns the following:</p>
  
  <pre><code>{
      "specialcol":"specialvalue",
      "Value":{
          "col1": "specialvalue",
          "col2": "someval",
          "col3": "someval"
      },
      "specialcol":"specialvalue2",
      "Value":{
          "col1": "specialvalue2",
          "col2": "someval2",
          "col3": "someval2"
      }
  }
  </code></pre>
  
  <p>Which is close, but not quite what I need.</p>
  
  <p>Is there a way to use JSON_MODIFY to accomplish what I'm trying to get?</p>