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
| 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.