I have the below input
The output will be
The Combined Match Played column is the sum of the values of Match Played column of those players. e.g. for Combined Played 1,2 the Combined Match Played value is 10 + 5 = 15. similarly, Combined Runs Made is the sum of the Runs MAde column of the individual players. e.g. for the same example, the Combined Runs MAde column is 200 +100 =300.
First of all, what you want is combinations not permutations.
The trick here is to generate a recursive cte that gives you all the combinations, summing the matches and runs is a simple next step. The combination cte is controlled by only adding playerids that are greater than the one you started with (`cte.playerid < m.playerid`), and where that playerid isn't already in the combination string (`playercomb not like '%'+ cast(m.playerid as varchar) + '%'`).
Is the ordering important?