1

I am following the idea from Ed.Schavelev on article 'SQL Server OPENJSON read nested json'. But I need instead of repeating the rows I wanted them (4) to be in the columns. Is this possible?

I have tried as below:

DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[  
      
   {
      "EmployeeScheduleXRefCode":"#DF_10212",
      "EmployeeXRefCode":"7274",
      "TimeStart":"2022-10-31T05:30:00",
      "TimeEnd":"2022-10-31T15:30:00",      
      "Published":false,
      "Breaks":[
         
      ],
      "Activities":[
         
      ],
      "Skills":[
         
      ],
      "LaborMetrics":[
         {
            "CodeXRefCode":"MEDIUM",
            "TypeXRefCode":"ACUITY_LM"
         },
         {
            "CodeXRefCode":"ATA",
            "TypeXRefCode":"EMPLOYEE_TYPE_LM"
         },
         {
            "CodeXRefCode":"BM02KR",
            "TypeXRefCode":"TEAM_NAME_LM"
         },
         {
            "CodeXRefCode":"AV_LM",
            "TypeXRefCode":"JOB_TYPE_LM"
         }
      ],
      "Segments":[
         
      ]
   },
   {
      "EmployeeScheduleXRefCode":"#DF_10200",
      "EmployeeXRefCode":"7269",
      "TimeStart":"2022-10-31T05:30:00",
      "TimeEnd":"2022-10-31T15:30:00",      
      "Published":false,
      "Breaks":[
         
      ],
      "Activities":[
         
      ],
      "Skills":[
         
      ],
      "LaborMetrics":[
         {
            "CodeXRefCode":"MEDIUM",
            "TypeXRefCode":"ACUITY_LM"
         },
         {
            "CodeXRefCode":"ATA",
            "TypeXRefCode":"EMPLOYEE_TYPE_LM"
         },
         {
            "CodeXRefCode":"BM01KR",
            "TypeXRefCode":"TEAM_NAME_LM"
         },
         {
            "CodeXRefCode":"AV_LM",
            "TypeXRefCode":"JOB_TYPE_LM"
         }
      ],
      "Segments":[
         
      ]
   }
 ]'  
   
SELECT Shifts.*,LaborMetrics.* FROM  
 OPENJSON ( @json )  
WITH (   
              EmployeeScheduleXRefCode   varchar(200) '$.EmployeeScheduleXRefCode' ,  
              EmployeeXRefCode     varchar(200)     '$.EmployeeXRefCode',
              TimeStart Datetime '$.TimeStart',
              TimeEnd Datetime '$.TimeEnd',
              LaborMetrics nvarchar(max) as json
 ) as Shifts
 cross apply openjson (Shifts.LaborMetrics)
 with
(
    CodeXRefCode nvarchar(100),
    TypeXRefCode nvarchar(100)
) as LaborMetrics

But the result is repeating 4 rows and I want them to have them in 4 different columns in a single row.

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.