1

I have a list of players in a tournament.

tournament=> SELECT * FROM players;
       name        | player_id
-------------------+-----------
 Twilight Sparkle  |         9
 Fluttershy        |        10
 Applejack         |        11
 Pinkie Pie        |        12
 Rarity            |        13
 Rainbow Dash      |        14
 Princess Celestia |        15
 Princess Luna     |        16
(8 rows)

This is how I would like the list to look. How do I ask postgreSQL to make it so?

       name1       |   id1   |       name2       |   id2
-------------------+---------+-------------------+-------
Twilight Sparkle   |    9    |  Fluttershy       |    10
Applejack          |    11   |  Pinkie Pie       |    12
Rarity             |    13   |  Rainbow Dash     |    14
Princess Celestia  |    15   |  Princess Luna    |    16
(4 pairs)
MetaG
  • 39
  • 10

1 Answers1

0

Solved! Unfortunately I could not find a way to do this with a simple SQL query. However, Thanks to a thread on StackOverFlow here I was able to find a solution using Python's itertools. It now returns 4 pairs instead of 28. I have passed all 10 tests! Below are my additions to tournament.py:

import itertools

swissPairings():

    standings = playerStandings()

    pairingsiterator = itertools.izip(*[iter(standings)]*2)
    # Iterate through the list and build the pairings
    results = []
    pairings = list(pairingsiterator)
    for pair in pairings:
        id1 = pair[0][0]
        name1 = pair[0][1]
        id2 = pair[1][0]
        name2 = pair[1][1]
        matchup = (id1, name1, id2, name2)
        results.append(matchup)
    return results
MetaG
  • 39
  • 10
  • It's called "pivot" or "crosstab" and indeed is better done in the application. SQL is not really suited for this. –  Oct 20 '17 at 06:01