How to do permutation in tsql (set based)

Question

I have the below input

PlayerID    MatchPlayed RunsMade
--------    ----------- --------
1           10              200
2           5               100
3           8               24
4           30              50

The output will be

Combined Players    Combined Match Played   Combined runs Made  
----------------    ---------------------   ------------------  
1                   10                      200         
1,2                 15                      300         
1,3                 18                      224
1,4                 40                      250
1,2,3               23                      324
1,2,4               45                      350
1,3,4               48                      274
1,2,3,4             53                      374
2                   5                       100
2,3                 13                      124
2,4                 35                      150
2,3,4               43                      174
3                   8                       24
3,4                 38                      74
4                   30                      50

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.

Thanks

Answer 1

Setup:

create table Input(PlayerId int, MatchPlayed int, RunsMade int)

insert Input
  select 1, 10, 200
  union all select 2, 5, 100
  union all select 3, 8, 24
  union all select 4, 30, 50

Query:

with cte(Combined, PlayerId, MatchPlayed, RunsMade)
as
(
    select cast(PlayerId as varchar(500)), PlayerId, MatchPlayed, RunsMade
    from Input
    union all
    select cast(cte.Combined + ',' + cast(inp.PlayerId as varchar) as varchar(500)), inp.PlayerId, inp.MatchPlayed + cte.MatchPlayed, inp.RunsMade + cte.RunsMade
    from cte
        join Input inp on
            cte.PlayerId < inp.PlayerId
)
select Combined, MatchPlayed, RunsMade
from cte
order by Combined