How define select, if bridge pair may have 3 players?

Question

Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players.

Table, members:

pair, player_id

1 1
1 2
2 3
2 4
3 5
3 6
4 7
4 8
5 9
5 10
6 11
6 12
6 13

table players:

id name

1 Smith
2 Brown
3 Johnson
4 Wild
5 Hammer
6 Bolt
7 Pink
8 Bush
9 Novak
10 King
11 Holmes
12 Watson
13 Gold

Required result of select is:

Pair, Names
1, Smith - Brown
2, Johnson - Wild
3, Hammer - Bolt
4, Pink - Bush
5, Novak - King
6, Holmes - Watson - Gold

Answer 1

This is the MySQL solution (very similar to Leigh's Oracle solution):

SELECT Pair
 , GROUP_CONCAT(name SEPARATOR ' - ') Names
FROM Members m
JOIN Players p ON m.player_id = p.id
GROUP BY Pair;

Obligatory sqlfiddle which was modified from Leigh's illustration as well.*

_{* I give credit when I plagiarize!}

Answer 2

This works in SQL Server 2005+ The "magic" is using XML to concatenate a group of strings per pair. Hope this helps!

WITH Pairs
(   
    Pair
)
AS
(
    SELECT DISTINCT
        m.pair AS Pair
    FROM dbo.members AS m
)
SELECT
    p.Pair,
    REPLACE
    (
        REPLACE
        (
            REPLACE
            (
                CONVERT(VARCHAR(MAX), X.n),
                '</PlayerName><PlayerName>',
                ' - '
            ),
            '</PlayerName>',
            ''
        ),
        '<PlayerName>',
        ''
    ) AS ConcatNames
FROM Pairs AS p

    CROSS APPLY --Create XML string
    (
        SELECT
            pl.name AS PlayerName
        FROM dbo.players AS pl

            INNER JOIN dbo.members AS m
                ON pl.id = m.player_id

        WHERE m.pair = p.Pair

        ORDER BY
            pl.id ASC

        FOR XML PATH(''), TYPE
    ) AS X(n)

ORDER BY
    p.Pair ASC;

Answer 3

The most preferable way to do this is to do whatever formatting you need to in something above the SQL layer. This is pretty easy to do in a lot of reporting tools or in languages which manipulate object arrays efficiently.

If you're using T-SQL, you can use FOR XML PATH to do the job.

select distinct
    mo.pair,
    names = stuff( 
                        (
                            select ' - ' + p.Name 
                             from players p 
                                inner join members m on m.player_id = p.id
                             where mo.pair = m.pair 
                             order by m.player_id 
                             for xml path ( '' ) 
                        ), 1, 3, ''
                    )
from
    members mo;

Answer 4

Oracle 11.2+ solution ^{(SQL Fiddle)}:

SELECT Pair, LISTAGG(Name,', ') WITHIN GROUP (ORDER BY id) Names
FROM Members m
JOIN Players p ON m.player_id = p.id
GROUP BY Pair;

Answer 5

Since the question is not tagged a specific DBMS, here's a "standards-compliant" solution - it stays away from FOR-XML, GROUP_CONCAT and LISTAGG tricks. It works for only up to 3 members in a "pair" (set). The code works as-is in PostgreSQL.

SELECT m1.Pair,
       p1.name || ' - ' || p2.name ||
              COALESCE(' - ' || p3.name, '') AS Names
FROM Members m1
JOIN Members m2 on m1.pair = m2.pair and m1.player_id < m2.player_id
LEFT JOIN Members m3 on m2.pair = m3.pair and m2.player_id < m3.player_id
JOIN Players p1 ON m1.player_id = p1.id
JOIN Players p2 ON m2.player_id = p2.id
LEFT JOIN Players p3 ON m3.player_id = p3.id
WHERE p3.id is not null or NOT EXISTS (
  SELECT *
  FROM Members m4
  WHERE m4.pair=m2.pair
  AND m4.player_id NOT IN (m1.player_id,m2.player_id))
ORDER BY m1.Pair;

The problem with standards is that, well, there are many. See here for conformance the string concatenation operator (||). For this solution to be applied to MySQL, the top bit has to be written using CONCAT()

SELECT m1.Pair,
       CONCAT(p1.name, ' - ', p2.name,
              COALESCE(Concat(' - ', p3.name), '')) AS Names

SQL Server uses the + operator.
Note: Even though SQL Server 2012 supports the CONCAT() function, it implements the same Oracle bug, i.e. CONCAT(' - ', NULL) => ' - ' instead of NULL.

SELECT m1.Pair,
       p1.name + ' - ' + p2.name +
              COALESCE(' - ' + p3.name, '') AS Names

Oracle will require a CASE statement to get around the concatenation-with-NULL bug mentioned above.

SELECT m1.Pair,
       p1.name || ' - ' || p2.name ||
       CASE when p3.name is null then '' else ' - ' || p3.name END AS Names

---

Performance

Despite the NOT EXISTS anti-semijoin, when put against Kevin and Matt's SQL Server solutions, it produces an estimated query cost of

Matt:Kevin:Richard = 24%: 65% : 11%

---

PostgreSQL 9.1+, SQL Server 2012+, Oracle, MySQL, DB2

The following query (SQLFiddle) proposed by Leigh in the comments works on all the listed DBMS:

SELECT m1.Pair
   , CONCAT(CONCAT(CONCAT(p1.name, ' - '), p2.name), 
        COALESCE((SELECT CONCAT(' - ', p3.name) FROM Players p4 WHERE p4.id = p3.id),'')) AS Names
FROM Members m1
JOIN Members m2 on m1.pair = m2.pair and m1.player_id < m2.player_id
LEFT JOIN Members m3 on m2.pair = m3.pair and m2.player_id < m3.player_id
JOIN Players p1 ON m1.player_id = p1.id
JOIN Players p2 ON m2.player_id = p2.id
LEFT JOIN Players p3 ON m3.player_id = p3.id
WHERE p3.id is not null or NOT EXISTS (
  SELECT *
  FROM Members m4
  WHERE m4.pair=m2.pair
  AND m4.player_id NOT IN (m1.player_id,m2.player_id))
ORDER BY m1.Pair

The real trick is in getting the string concatenation correct, SQL Server is the last on the list to add a CONCAT() function. However, a function by the same name across 5 DBMS is by no means a standard, since it behaves differently. Already mentioned above is that Oracle treats NULLs in CONCAT as empty strings (''), and PostgreSQL requires that operands are strings (will not auto-cast).