title: Extract a JSON array items to a single column separated with comma
body: <p>Struggling to find an answer to the below JSON problem. I would like to display the entire <code>SKU/Quantity</code> list in the <code>&quot;shipmentItems&quot;</code> array to their respective column with a comma-separated value. My example below only allows me to display the first SKU/quantity from the array but, my goal is to get all listed in the columns with comma-separated.</p>
  <p>JSON example:</p>
  <pre><code>{&quot;shipments&quot;: [
      {
          &quot;shipmentId&quot;: 100003768,
          &quot;orderNumber&quot;: &quot;9648219086&quot;,
          &quot;shipDate&quot;: &quot;2021-10-28&quot;,
          &quot;serviceCode&quot;: &quot;ups_ground&quot;,
          &quot;shipmentItems&quot;: [
              {
                  &quot;orderItemId&quot;: 1464643208,
                  &quot;lineItemKey&quot;: &quot;10322938560608&quot;,
                  &quot;sku&quot;: &quot;SCPXTSS-BAG-06&quot;,
                  &quot;name&quot;: &quot;SCOOP-PLATE-06 (1000ml)&quot;,
                  &quot;weight&quot;: {
                      &quot;value&quot;: 0,
                      &quot;units&quot;: &quot;ounces&quot;,
                      &quot;WeightUnits&quot;: 1
                  },
                  &quot;quantity&quot;: 1,
                  &quot;unitPrice&quot;: 0,
                  &quot;taxAmount&quot;: null
                  },
              {
                  &quot;orderItemId&quot;: 1464643207,
                  &quot;lineItemKey&quot;: &quot;10322938527840&quot;,
                  &quot;sku&quot;: &quot;SCPZRTS-TRAY-01&quot;,
                  &quot;name&quot;: &quot;Beef: Tray 3 (Fill 004)&lt;br&gt;&quot;,
                  &quot;weight&quot;: {
                      &quot;value&quot;: 60,
                      &quot;units&quot;: &quot;ounces&quot;,
                      &quot;WeightUnits&quot;: 1
                  },
                  &quot;quantity&quot;: 1,
                  &quot;unitPrice&quot;: 102.72,
                  &quot;taxAmount&quot;: null
                  }
          ],
          &quot;labelData&quot;: null,
          &quot;formData&quot;: null
      }
   
  ]
  }
  </code></pre>
  <p>SQL query I'm using:</p>
  <pre><code>DECLARE @JSON varchar(max)
  
  SELECT @JSON = BulkColumn
  FROM OPENROWSET (BULK 'C:\Users\XPS-LT\json\today\shipments_20211031.json', SINGLE_CLOB)
  IMPORT
  SELECT *
  FROM OPENJSON (@JSON, '$.shipments') 
  WITH 
  (
      [shipmentId] bigint, 
      [orderNumber] nvarchar(60), 
      [shipDate] date, 
      [serviceCode] nvarchar(30), 
      [sku] nvarchar(MAX) N'$.shipmentItems[0].sku',
      [quantity] int N'$.shipmentItems[0].quantity' 
  )          
  ;
  </code></pre>
  <p><img src="https://i.stack.imgur.com/Y0Wev.png" alt="Current result " /></p>