title: how to Convert Table to Json Arrays?
body: <p>I'm using Sql Server 2016 and I want to convert a table to json.</p>
  
  <p>I have a simple Table :</p>
  
  <pre><code>CREATE TABLE [dbo].[TableTmp](
      [Color] [nvarchar](50) NULL,
      [Type] [nvarchar](50) NULL,
      [Number] [nvarchar](50) NULL
  ) ON [PRIMARY]
  GO
  INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'A', N'1')
  GO
  INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'A', N'2')
  GO
  INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'A', N'3')
  GO
  INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'B', N'1')
  GO
  INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Blue', N'C', N'1')
  GO
  INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Red', N'A', N'1')
  GO
  INSERT [dbo].[TableTmp] ([Color], [Type], [Number]) VALUES (N'Red', N'B', N'2')
  GO
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/0mwjx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0mwjx.png" alt="enter image description here"></a></p>
  
  <p>I want to generate a JSON string like this:</p>
  
  <pre><code>[
    {
      "Color": "Blue",
      "Part": [
        {
          "Type": "A",
          "Number": [
            "1",
            "2",
            "3"
          ]
        },
        {
          "Type": "B",
          "Number": [
            "1"
          ]
        },
        {
          "Type": "C",
          "Number": [
            "1"
          ]
        }
      ]
    },
    {
      "Color": "Red",
      "Part": [
        {
          "Type": "A",
          "Number": [
            "1"
          ]
        },
        {
          "Type": "B",
          "Number": [
            "2"
          ]
        }
      ]
    }
  ]
  </code></pre>
  
  <p>There can be more <strong>colors</strong> and/or <strong>types</strong>. How can I do this?</p>
  
  <p>If you need more details, I'll be happy to share. I'm currently feeling that I have passed on all that is needed to understand the problem.</p>