title: Get SQL server record details as JSON for each record individually
body: <p>I have 2 tables. Department &amp; Employee as below :
     <pre>
      <strong>Department</strong>
      |----------------------|
      |   Id    |   Name     |
      |---------|------------|
      |   10    |    Admin   |
      |---------|------------|
      |   11    |    IT      |
      |---------|------------|<br>
      <strong>Employee</strong>
      |----------------------|--------------|
      |   Id    |   Name     | DepartmentId |
      |---------|------------|--------------|
      |   1     |    Peter   |    10        |
      |---------|------------|--------------|
      |   2     |    Scott   |    11        |
       --------------------------------------</p>
  
  <p></pre></p>
  
  <p>I need the JSON details of each department individually as below:</p>
  
  <pre><code>|----------------------|------------------------------------------------------------ |
  |   DepartmentId       |   JSONDeatails                                              |
  |--------------------- |------------------------------------------------------------ |
  |   10                 |{"id": 10,"name": "Admin","Emp": [{"Id": 1,"name": "peter"}]}|
  |----------------------|-------------------------------------------------------------|
  |   11                 |{"id": 11,"name": "IT","Emp":[{"Id": 2,"name": "scott"}]}    |
  |----------------------|-------------------------------------------------------------|
  </code></pre>
  
  <p>I tried below query which is returning the entire table data as one JSON.</p>
  
  <pre><code> SELECT     D.Id, D.name, Emp.Id, Emp.Name FROM Department D
   LEFT JOIN Employee Emp ON D.Id= Emp.DepartmentId FOR JSON AUTO
  </code></pre>
  
  <p>I am getting the response as below:</p>
  
  <pre><code>JSON_F52E2B61-18A1-11d1-B105-00805F49916B
  [{"id": 10,"name": "Admin","Emp": [{"Id": 1,"name": "peter"}]},{"id": 11,"name": "IT","Emp":[{"Id": 2,"name": "scott"}]}]
  </code></pre>