title: Grouping results from stored procedure as JSON - Azure SQL
body: <p>I have a Stored proc returning data in roughly this format</p>
  
  <pre><code>ID | Field1 | Field2 | Group  | Description
  ------------------------------------------------
  1  |   A    |   A    |   g1   |  g1A description
  1  |   A    |   A    |   g2   |  g2A description
  2  |   B    |   B    |   g1   |  g1B description
  2  |   B    |   B    |   g2   |  g2B description
  3  |   C    |   C    |   g1   |  g1C description
  3  |   C    |   C    |   g2   |  g2C description
  
  
  SELECT
    a.ID,
    a.Field1,
    b.Field2,
    c.Group,
    c.Description
  FROM A as a
    INNER JOIN B b ...
    INNER JOIN C c ...
  </code></pre>
  
  <p>In code, I am converting the dataset to JSON</p>
  
  <pre><code>[
    {
      ID: 1,
      Field1: A,
      Field2: A,
      Groups: {
        g1: g1A description,
        g2: g2A description,
      }
    }, {
      ID: 2,
      Field1: B,
      Field2: B,
      Groups: {
        g1: g1B description,
        g2: g2B description,
      }
    }, {
      ID: 1,
      Field1: C,
      Field2: C,
      Groups: {
        g1: g1C description,
        g2: g2C description,
      }
    }
  ]
  </code></pre>
  
  <p>I don't like that the procedure is returning mostly duplicate data in each row. Is there some way I can perform the grouping in SQL, so that the returned dataset is something like</p>
  
  <pre><code>ID | Field1 | Field2 | Groups  
  ------------------------------------------------
  1  |   A    |   A    | 'Groups: { g1: g1A description, g2: g2A description }'
  2  |   B    |   B    | 'Groups: { g1: g1B description, g2: g2B description }'
  3  |   C    |   C    | 'Groups: { g1: g1C description, g2: g2C description }'
  </code></pre>
  
  <p>OR</p>
  
  <pre><code> ID | Field1 | Field2 |        g1       |        g2
  ----------------------------------------------------------
   1  |   A    |   A    | g1A description | g2A description
   2  |   B    |   B    | g1B description | g2B description
   3  |   C    |   C    | g1C description | g2C description
  </code></pre>
  
  <hr>
  
  <p>Using the answer from 'D-Shih, I came up with this:</p>
  
  <pre><code>   SELECT
         a.ID,
         a.Field1,
         b.Field2,
         (
           SELECT 
             MAX(CASE WHEN [Group] = 'g1' THEN Description END) g1, 
             MAX(CASE WHEN [Group] = 'g2' THEN Description END) g2
           FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
         ) as Groups
     FROM A as a
        INNER JOIN B b ...
        INNER JOIN C c ...
  
  
      ID | Field1 | Field2 | Groups  
     ------------------------------------------------
      1  |   A    |   A    | '{ "g1": "g1A description", "g2": "g2A description" }'
      2  |   B    |   B    | '{ "g1": "g1B description", "g2": "g2B description" }'
      3  |   C    |   C    | '{ "g1": "g1C description", "g2": "g2C description" }'
  </code></pre>
  
  <p><a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=4bf8a009fe91bbbd0f3246723a1f9eb4" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=4bf8a009fe91bbbd0f3246723a1f9eb4</a></p>