Agfdhyk

I have a Stored proc returning data in roughly this format

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 ...

In code, I am converting the dataset to JSON

[

  {

    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,

    }

  }

]

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

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 }'

OR

 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

Using the answer from 'D-Shih, I came up with this:

   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" }'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4bf8a009fe91bbbd0f3246723a1f9eb4

Your second expect result can use condition aggregate function to make it.

SELECT ID,

      Field1,

      Field2,

      MAX(CASE WHEN [Group] = 'g1' THEN Description END) g1, 

      MAX(CASE WHEN [Group] = 'g2' THEN Description END) g2

FROM T

GROUP BY ID,Field1,Field2

SQLFIDDLE

NOTE

T is your currently result set.