Split String and Summarise Values into Columns

Question

I have a table full of strings that look like the following: P12D1P13D1P2D47R1D37P3D1R10D10

What would be the best way to split the string by P, D and R and then SUM per each letter group?

For example summarised as columns:

P	D	R
30	97	11

Answer 1

Here is an option using CROSS APPLY and string_split() and a conditional aggregation

Example

Declare @YourTable Table (id int,[SomeCol] varchar(50))  Insert Into @YourTable Values 
 (1,'P12D1P13D1P2D47R1D37P3D1R10D10')
 
Select A.id
      ,C.*
 From  @YourTable A
 Cross Apply ( values (replace(replace(replace(SomeCol,'P','|P'),'D','|D'),'R','|R'))) B(S)
 Cross Apply ( Select D = sum(case when Item='D' then Val end)
                     ,P = sum(case when Item='P' then Val end)
                     ,R = sum(case when Item='R' then Val end)
                From  string_split(S,'|')  C1
                Cross Apply (values (left(value,1),try_convert(int,substring(value,2,25)))) C2(Item,Val)
             ) C

Results

id  D   P   R
1   97  30  11