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, 2011 at 12:06 AM in Default

avatar 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, 2011 at 12:31 AM Mrs_Fatherjack

@mrs_fatherjack - looks like it could be used for a fantasy cricket league may be?

Jun 08, 2011 at 12:58 AM Fatherjack ♦♦

Right.. something of that sort

Jun 08, 2011 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 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?

more ▼

answered Jun 08, 2011 at 02:38 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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, 2011 at 02:57 AM Fatherjack ♦♦

Yes I should have pointed out that this will bomb out at 100 recursions by default

Jun 08, 2011 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, 2011 at 03:11 AM Fatherjack ♦♦

That's one heck of a cricket team

Jun 08, 2011 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, 2011 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.

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:

x2017
x393

asked: Jun 08, 2011 at 12:06 AM

Seen: 1996 times

Last Updated: Jun 08, 2011 at 12:58 AM

Copyright 2016 Redgate Software. Privacy Policy