By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{"q1": "B", "q2": "B"},
{"q1": "C", "q2": "C"},
{"q1": "D", "q2": "C"},
{"q1": "A", "q2": "B"},
{"q1": "A", "q2": "B"},
{"q1": "C", "q2": "A"},
{"q1": "C", "q2": "B"}
]';
SELECT
v.Question,
sum(case when v.val = 'A' then 1 else 0 end) as A,
sum(case when v.val = 'B' then 1 else 0 end) as B,
sum(case when v.val = 'C' then 1 else 0 end) as C,
sum(case when v.val = 'D' then 1 else 0 end) as D
FROM OPENJSON(@json)
WITH (
q1 NVARCHAR(50) '$.q1',
q2 NVARCHAR(50) '$.q2'
) CROSS APPLY (VALUES ('q1',q1),('q2',q2)) v(Question,val)
GROUP BY v.Question
| Question | A | B | C | D |
|---|---|---|---|---|
| q1 | 2 | 1 | 3 | 1 |
| q2 | 1 | 4 | 2 | 0 |