SQL Server 2017

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

DECLARE @JsonData varchar(max)

SET @JsonData = 
'{"status":"OK","code":200,"trace_id":"fdfdsgdgfsgdfs","timestamp":"2023-09-12T17:24:20Z","messages": [],"result": {
  "response_metadata" : {
    "total_number_of_instruments" : 2,
    "total_number_of_unresolved_identifiers" : 0,
    "total_number_of_batches" : 1,
    "current_batch" : 1,
    "data_request_id" : "0-gfsgsd17:24:20+0000",
    "data_request_id_expiration_time" : "2023-09-13T17:24:20+0000",
    "resolvedfactors" : [ "ESG_RATING", "ISSUER_NAME", "ISSUER_ISIN", "ISSUER_TICKER", "ISSUERID" ]
  },
  "data" : [ {
    "requested_id" : "IID000000003053597",
    "issuer_metadata" : [ {
      "ISSUERID" : "IID000000003053597",
      "ISSUER_NAME" : "Crowdstrike Holdings Inc",
      "ISSUER_ISIN" : "US22788C1053",
      "as_of_date" : "2019-09-01",
      "valid_until_date" : "2019-12-01"
    } ],
    "factors" : [ {
      "name" : "ISSUER_NAME",
      "data_values" : [ {
        "value" : "Crowdstrike Holdings Inc",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.921118Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "Crowdstrike Holdings Inc",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.921118Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "Crowdstrike Holdings Inc",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.921118Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_ISIN",
      "data_values" : [ {
        "value" : "US22788C1053",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.921091Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US22788C1053",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.921091Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US22788C1053",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.921091Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUERID",
      "data_values" : [ {
        "value" : "IID000000003053597",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000003053597",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000003053597",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    } ]
  }, {
    "requested_id" : "IID000000002633749",
    "issuer_metadata" : [ {
      "ISSUERID" : "IID000000002633749",
      "ISSUER_NAME" : "CRESTWOOD MIDSTREAM PARTNERS LP",
      "ISSUER_ISIN" : "US226373AL21",
      "ISSUER_TICKER" : "CEQP",
      "as_of_date" : "2019-09-01",
      "valid_until_date" : "2019-12-01"
    } ],
    "factors" : [ {
      "name" : "ESG_RATING",
      "data_values" : [ {
        "value" : "B",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925397Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "B",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925397Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "B",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925397Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_NAME",
      "data_values" : [ {
        "value" : "CRESTWOOD MIDSTREAM PARTNERS LP",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925444Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CRESTWOOD MIDSTREAM PARTNERS LP",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925444Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CRESTWOOD MIDSTREAM PARTNERS LP",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925444Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_ISIN",
      "data_values" : [ {
        "value" : "US226373AL21",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925421Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US226373AL21",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925421Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US226373AL21",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925421Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_TICKER",
      "data_values" : [ {
        "value" : "CEQP",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925464Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CEQP",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925464Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CEQP",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925464Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUERID",
      "data_values" : [ {
        "value" : "IID000000002633749",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000002633749",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000002633749",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    } ]
  } ]
}}';
select t.data_idx, t.factors_idx, t.factor_name, d.[key] val_idx, 
     max(case e.[key] when 'value' then e.value end) [value],
     max(case e.[key] when 'as_of_date' then e.value end) as_of_date,
     max(case e.[key] when 'as_at_date' then e.value end) as_at_date,
     max(case e.[key] when 'reference_issuer_id' then e.value end) reference_issuer_id,
     max(case e.[key] when 'reference_issuer_name' then e.value end) reference_issuer_name
from (
     select a.[key] data_idx,  b.[key] factors_idx, 
       max(case c.[key] when 'name' then c.value end) factor_name,
       max(case c.[key] when 'data_values' then c.value end) factor_values 
     from openjson(@JsonData, '$.result.data') a
     cross apply openjson(a.value, '$.factors') b
     cross apply openjson(b.value, '$') c
     group by a.[key],  b.[key]
    ) t
cross apply openjson(t.factor_values,'$') d
cross apply openjson(d.[value], '$') e
group by t.data_idx, t.factors_idx, t.factor_name, d.[key]

data_idx	factors_idx	factor_name	val_idx	value	as_of_date	as_at_date	reference_issuer_id	reference_issuer_name
0	0	ISSUER_NAME	0	Crowdstrike Holdings Inc	2019-09-30	2023-09-12T17:24:20.921118Z	null	null
0	0	ISSUER_NAME	1	Crowdstrike Holdings Inc	2019-10-31	2023-09-12T17:24:20.921118Z	null	null
0	0	ISSUER_NAME	2	Crowdstrike Holdings Inc	2019-11-29	2023-09-12T17:24:20.921118Z	null	null
0	1	ISSUER_ISIN	0	US22788C1053	2019-09-30	2023-09-12T17:24:20.921091Z	null	null
0	1	ISSUER_ISIN	1	US22788C1053	2019-10-31	2023-09-12T17:24:20.921091Z	null	null
0	1	ISSUER_ISIN	2	US22788C1053	2019-11-29	2023-09-12T17:24:20.921091Z	null	null
0	2	ISSUERID	0	IID000000003053597	2019-09-30	2023-09-12T17:24:00Z	null	null
0	2	ISSUERID	1	IID000000003053597	2019-10-31	2023-09-12T17:24:00Z	null	null
0	2	ISSUERID	2	IID000000003053597	2019-11-29	2023-09-12T17:24:00Z	null	null
1	0	ESG_RATING	0	B	2019-09-30	2023-09-12T17:24:20.925397Z	null	null
1	0	ESG_RATING	1	B	2019-10-31	2023-09-12T17:24:20.925397Z	null	null
1	0	ESG_RATING	2	B	2019-11-29	2023-09-12T17:24:20.925397Z	null	null
1	1	ISSUER_NAME	0	CRESTWOOD MIDSTREAM PARTNERS LP	2019-09-30	2023-09-12T17:24:20.925444Z	null	null
1	1	ISSUER_NAME	1	CRESTWOOD MIDSTREAM PARTNERS LP	2019-10-31	2023-09-12T17:24:20.925444Z	null	null
1	1	ISSUER_NAME	2	CRESTWOOD MIDSTREAM PARTNERS LP	2019-11-29	2023-09-12T17:24:20.925444Z	null	null
1	2	ISSUER_ISIN	0	US226373AL21	2019-09-30	2023-09-12T17:24:20.925421Z	null	null
1	2	ISSUER_ISIN	1	US226373AL21	2019-10-31	2023-09-12T17:24:20.925421Z	null	null
1	2	ISSUER_ISIN	2	US226373AL21	2019-11-29	2023-09-12T17:24:20.925421Z	null	null
1	3	ISSUER_TICKER	0	CEQP	2019-09-30	2023-09-12T17:24:20.925464Z	null	null
1	3	ISSUER_TICKER	1	CEQP	2019-10-31	2023-09-12T17:24:20.925464Z	null	null
1	3	ISSUER_TICKER	2	CEQP	2019-11-29	2023-09-12T17:24:20.925464Z	null	null
1	4	ISSUERID	0	IID000000002633749	2019-09-30	2023-09-12T17:24:00Z	null	null
1	4	ISSUERID	1	IID000000002633749	2019-10-31	2023-09-12T17:24:00Z	null	null
1	4	ISSUERID	2	IID000000002633749	2019-11-29	2023-09-12T17:24:00Z	null	null

Warning: Null value is eliminated by an aggregate or other SET operation.