SQL Server 2019

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

CREATE TABLE #temp
(
    Id INT NOT NULL PRIMARY KEY
  , Attributes NVARCHAR(MAX) NULL
);

INSERT INTO #temp (Id, Attributes)
VALUES (1, '[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]');
INSERT INTO #temp (Id, Attributes)
VALUES (2, '[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]');
INSERT INTO #temp (Id, Attributes)
VALUES (3, '[{"Name":"State","Value":"Active"}]');
INSERT INTO #temp (Id, Attributes)
VALUES (4, '[{"Name":"Step","Value":"D"}]');

SELECT
    t.Id
  , t.Attributes
  , [Step] = MAX(CASE WHEN stepname.Name = 'Step' THEN stepname.Value END)
  , [State] = MAX(CASE WHEN statename.Name = 'State' THEN statename.Value END) 
FROM #temp t
CROSS APPLY
    OPENJSON(t.Attributes)
    WITH
    (
        Name NVARCHAR(MAX) '$.Name'
      , Value NVARCHAR(MAX) '$.Value'
    ) AS stepname
    CROSS APPLY
    OPENJSON(t.Attributes)
    WITH
    (
        Name NVARCHAR(MAX) '$.Name'
      , Value NVARCHAR(MAX) '$.Value'
    ) AS statename
GROUP BY t.Id, t.Attributes
ORDER BY t.Id

Id	Attributes	Step	State
1	[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]	A	Active
2	[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]	B	Inactive
3	[{"Name":"State","Value":"Active"}]	null	Active
4	[{"Name":"Step","Value":"D"}]	D	null

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