x

Generate permutation in sql server (set based)

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
more ▼

asked Jun 08 '11 at 12:06 AM in Default

newbie_1234 gravatar image

newbie_1234
11 1 1 1

What are you actually looking to achieve with this? What is the question you are actually asking?
Jun 08 '11 at 12:31 AM Mrs_Fatherjack
@mrs_fatherjack - looks like it could be used for a fantasy cricket league may be?
Jun 08 '11 at 12:58 AM Fatherjack ♦♦
Right.. something of that sort
Jun 08 '11 at 01:32 AM newbie_1234
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

First of all, what you want is combinations not permutations.
See this [article from Steve Jones][1] for a good discussion of the differences

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) + '%').

Try this

declare @Matches table (
    PlayerID int,
    MatchPlayed int,
    RunsMade int
    )

insert into @Matches select 1,10,200
insert into @Matches select 2,5,100
insert into @Matches select 3,8,24
insert into @Matches select 4,30,50

--select * from @matches

;with cte (playerid, playercomb, matchplayed, RunsMade) 
as 
(
select 
    playerid, 
    cast(playerid as varchar(max)), 
    matchplayed,
    RunsMade
from @matches
union all
    select 
       m.playerid, 
       playercomb +','+cast(m.playerid as varchar), 
       cte.matchplayed + m.matchplayed,
       cte.RunsMade + m.RunsMade
from cte 
join @matches m on cte.playerid < m.playerid 
                   and playercomb not like '%'+ cast(m.playerid as varchar) + ',%'
)

select playercomb, matchplayed, RunsMade
from cte 

Is the ordering important?

[1]: http://www.sqlservercentral.com/blogs/steve_jones/archive/2011/06/07/combinations-and-permutations.aspx
more ▼

answered Jun 08 '11 at 02:38 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 45 49 76

You want to watch out for the number of combinations here - 10 players is over 1000 combinations, 20 is well over 1M results....

If you want to test it (please, on a DEV server!) change the playerid to be an identity, comment out Kev's insert code and use

WHILE (SELECT count(*) from @Matches) < 20
BEGIN
 insert into @Matches (matchPlayed, runsmade) select  abs(checksum(newid()))%20 +1, abs(checksum(newid()))%400 +1
END
Jun 08 '11 at 02:57 AM Fatherjack ♦♦
Yes I should have pointed out that this will bomb out at 100 recursions by default
Jun 08 '11 at 02:59 AM Kev Riley ♦♦
!!! that would be 1,267,650,600,225,229,401,496,703,205,376 combinations. Better have a decent processor
Jun 08 '11 at 03:11 AM Fatherjack ♦♦
That's one heck of a cricket team
Jun 08 '11 at 08:16 AM Kev Riley ♦♦
not really, if this is for something like a fantasy cricket league then comparison of all players in games vs all players (i.e. over a season) then you might have 10-15 players per team and maybe 10 teams in a league/division. What you would have to do is keep out the combinations that didnt play - rather than allowing for all combinations. I think if thats the case then the schema needs to take into account a fixtures table and a teams table so that the joins would prevent over linking of players details.
Jun 08 '11 at 08:52 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x265

asked: Jun 08 '11 at 12:06 AM

Seen: 1330 times

Last Updated: Jun 08 '11 at 12:58 AM