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
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!
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;
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;
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;
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
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%
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).
