SQL Server : Request for extracting the strings following each occurence of same substrings

I have a string with this format

"AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16"

and I would like to put the numbers in different columns, giving to each column the name you can find in the string before the =associated with this number.

So here, I would like to have 7 columns like that

Edit: This structure is fixed, and we'll always have one AB, three AC, and three AD.

My problem is that we have 3 occurrences of AC and 3 occurrences of AD, and I can't reach data contained in the 2nd and 3rd occurrence of AC and AD. I tried with PATINDEX and CHARINDEX and always reach only the first occurrence.

Is it possible to do it in a single SELECT query?

You may try a JSON-based approach. You need to transform the input into a valid JSON with the appropriate structure ('AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16 into [{"AB":10},{"AC":11},{"AC":12},{"AC":13},{"AD":14},{"AD":15},{"AD":16}) and parse it with JSON_VALUE():

DECLARE @input nvarchar(max) = N'AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16'
SET @input = CONCAT('[{"', REPLACE(REPLACE(@input, ',', '"},{"'), '=', '":"'), '"}]')

SELECT 
   JSON_VALUE(@input, '$[0].AB') AS AB,
   JSON_VALUE(@input, '$[1].AC') AS AC1,
   JSON_VALUE(@input, '$[2].AC') AS AC2,
   JSON_VALUE(@input, '$[3].AC') AS AC3,
   JSON_VALUE(@input, '$[4].AD') AS AD1,
   JSON_VALUE(@input, '$[5].AD') AS AD2,
   JSON_VALUE(@input, '$[6].AD') AS AD3

Result:

AB  AC1 AC2 AC3 AD1 AD2 AD3
10  11  12  13  14  15  16

If the data is stored in a table:

SELECT 
   t.Input,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[0].AB') AS AB,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[1].AC') AS AC1,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[2].AC') AS AC2,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[3].AC') AS AC3,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[4].AD') AS AD1,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[5].AD') AS AD2,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[6].AD') AS AD3
-- change the FROM clause with the real table and column names
FROM (VALUES (N'AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16')) t (Input)