2

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

5 Answers 5

4

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!

1
  • 1
    Credit to Lamak as my SQL Fiddle was modified from his. Nov 14, 2012 at 16:51
3

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;
2
  • 1
    Looks like we came up with about the same response at about the same time. Although I think the shorter version is easier to read, yours is more efficient (not needing a worktable), so +1 for you. Nov 14, 2012 at 13:30
  • +1 This is also working. An, another sqlfiddle with a demo: sqlfiddle.com/#!3/f68b9/7
    – Lamak
    Nov 14, 2012 at 13:33
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;
4
  • +1, This works great. Here is an sqlfiddle with this example: sqlfiddle.com/#!3/f68b9/6
    – Lamak
    Nov 14, 2012 at 13:30
  • +1 for mentioning that the preferable way to perform this is outside of T-SQL. For kicks, I compared the execution plans of our two solutions, by running them in the same batch and forcing a plan recompile. I show that your query costs 73% relative to the batch, and mine costs 27%. For this small sample, this means next to nothing. However, I would be interested to see how both of our queries scale to a large data set. Good stuff!
    – Matt M
    Nov 14, 2012 at 13:38
  • Thank Kevin for your reply. How to implement in MYSQL?
    – otm
    Nov 14, 2012 at 14:31
  • I'm not very familiar with MySQL, but you can try the bottom of this post: postgresonline.com/journal/archives/…. It shows how to do something similar to this for Postgres, SQL Server, and MySQL. It looks like it's actually really easy to do in MySQL. Nov 14, 2012 at 14:40
3

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;
1
3

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).

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.