SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @jsonVal varchar(max)
SET @jsonVal = '{
  "status": "OK",
  "code": 200,
  "trace_id": "3eea64f2a7917c11",
  "timestamp": "2023-05-31T14:36:02Z",
  "messages": [],
  "result": {
    "response_metadata": {
      "total_number_of_instruments": 1,
      "data_request_id_expiration_time": "2023-06-01T14:36:02+0000",
      "resolvedfactors": [
        "CREDIT_RTG",
        "ISSUER_NAME",
        "ISSUER_ISIN",
        "ISSUER_SEDOL",
        "ISSUERID"
      ]
    },
    "data": [
      {
        "requested_id": "IID000000002745031",
        "issuer_metadata": [
          {
            "ISSUERID": "IID000000002745031",
            "ISSUER_NAME": "ALPHABET INC.",
            "ISSUER_ISIN": "US02079K3059",
            "ISSUER_TICKER": "GOOGL",
            "as_of_date": "2019-09-01",
            "valid_until_date": "2019-09-14"
          },
          {
            "ISSUERID": "IID000000002745031",
            "ISSUER_NAME": "ALPHABET INC.",
            "ISSUER_ISIN": "US02079K1079",
            "ISSUER_TICKER": "GOOGL",
            "as_of_date": "2019-09-14",
            "valid_until_date": "2019-12-01"
          }
        ],
        "factors": [
          {
            "name": "CREDIT_RTG",
            "data_values": [
              {
                "value": "AA",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.245957Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "AA",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.245957Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "AA",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.245957Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUER_NAME",
            "data_values": [
              {
                "value": "ALPHABET INC.",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.246042Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "ALPHABET INC.",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.246042Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "ALPHABET INC.",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.246042Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUER_ISIN",
            "data_values": [
              {
                "value": "US02079K1079",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.246006Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "US02079K1079",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.246006Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "US02079K1079",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.246006Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUER_SEDOL",
            "data_values": [
              {
                "value": "BYY88Y7",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.246084Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "BYY88Y7",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.246084Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "BYY88Y7",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.246084Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUERID",
            "data_values": [
              {
                "value": "IID000000002745031",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:00Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "IID000000002745031",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:00Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "IID000000002745031",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:00Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          }
        ]
      }
    ]
  }
}'

SELECT
  final.*
FROM OPENJSON(@jsonVal,'$.result.data')
  WITH
  (
    factors nvarchar(max) AS JSON
  ) j1
CROSS APPLY (
    SELECT pvt.*
    FROM OPENJSON(j1.factors)
      WITH
      (
        name varchar(50),
        data_values nvarchar(max) AS JSON
      ) j2
    PIVOT (MAX(data_values) FOR name IN
      (
        ISSUERID, ISSUER_NAME, CREDIT_RTG
      )
    ) pvt
) pvt
CROSS APPLY (
    SELECT
      [ISSUERID] = issuerid.value,
      [ISSUER_NAME] = JSON_VALUE(arrISSUER_NAME.value, '$.value'),
      CREDIT_RTG    = JSON_VALUE(arrCREDIT_RTG.value,  '$.value'),
      issuerid.as_of_date
    FROM OPENJSON(pvt.ISSUERID) arrISSUERID
    JOIN OPENJSON(pvt.ISSUER_NAME) arrISSUER_NAME ON arrISSUER_NAME.[key] = arrISSUERID.[key]
    JOIN OPENJSON(pvt.CREDIT_RTG ) arrCREDIT_RTG  ON arrCREDIT_RTG.[key]  = arrISSUERID.[key]
    CROSS APPLY OPENJSON(arrISSUERID.value)
      WITH
      (
        value varchar(50),
        as_of_date date
      ) issuerid
) final;

-- if there is only one "data"

SELECT
  final.*
FROM (
    SELECT pvt.*
    FROM OPENJSON(@jsonVal,'$.result.data[0].factors')
      WITH
      (
        name varchar(50),
        data_values nvarchar(max) AS JSON
      ) j2
    PIVOT (MAX(data_values) FOR name IN
      (
        ISSUERID, ISSUER_NAME, CREDIT_RTG
      )
    ) pvt
) pvt
CROSS APPLY (
    SELECT
      [ISSUERID] = issuerid.value,
      [ISSUER_NAME] = JSON_VALUE(arrISSUER_NAME.value, '$.value'),
      CREDIT_RTG    = JSON_VALUE(arrCREDIT_RTG.value,  '$.value'),
      issuerid.as_of_date
    FROM OPENJSON(pvt.ISSUERID) arrISSUERID
    JOIN OPENJSON(pvt.ISSUER_NAME) arrISSUER_NAME ON arrISSUER_NAME.[key] = arrISSUERID.[key]
    JOIN OPENJSON(pvt.CREDIT_RTG ) arrCREDIT_RTG  ON arrCREDIT_RTG.[key]  = arrISSUERID.[key]
    CROSS APPLY OPENJSON(arrISSUERID.value)
      WITH
      (
        value varchar(50),
        as_of_date date
      ) issuerid
) final;

ISSUERID	ISSUER_NAME	CREDIT_RTG	as_of_date
IID000000002745031	ALPHABET INC.	AA	2019-09-30
IID000000002745031	ALPHABET INC.	AA	2019-10-31
IID000000002745031	ALPHABET INC.	AA	2019-11-29

ISSUERID	ISSUER_NAME	CREDIT_RTG	as_of_date
IID000000002745031	ALPHABET INC.	AA	2019-09-30
IID000000002745031	ALPHABET INC.	AA	2019-10-31
IID000000002745031	ALPHABET INC.	AA	2019-11-29