Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

5 Answers

up vote 4 down vote accepted

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!

share|improve this answer
1  
Credit to Lamak as my SQL Fiddle was modified from his. –  Leigh Riffel Nov 14 '12 at 16:51
add comment

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;
share|improve this answer
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. –  Kevin Feasel Nov 14 '12 at 13:30
    
+1 This is also working. An, another sqlfiddle with a demo: sqlfiddle.com/#!3/f68b9/7 –  Lamak Nov 14 '12 at 13:33
add comment

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;
share|improve this answer
    
+1, This works great. Here is an sqlfiddle with this example: sqlfiddle.com/#!3/f68b9/6 –  Lamak Nov 14 '12 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 '12 at 13:38
    
Thank Kevin for your reply. How to implement in MYSQL? –  otm Nov 14 '12 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. –  Kevin Feasel Nov 14 '12 at 14:40
add comment

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;
share|improve this answer
    
Here is the PostgreSQL version of your SQLFiddle: sqlfiddle.com/#!12/4ae8b/6 –  a_horse_with_no_name Nov 15 '12 at 12:37
add comment

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

share|improve this answer
    
+1 for a very complete answer an for showing another option. That said, I think that this solution is rather limited to the example posted and is difficult to create a solution with an unknown number of elements to concatenate –  Lamak Nov 15 '12 at 2:21
    
@Lamak So you're saying it's not an answer to a question titled "may have 3 players"? –  孔夫子 Nov 15 '12 at 2:40
    
Nope, I do think that is a very thorough answer, thats why I upvoted it; if I thougt otherwise I would'nt have –  Lamak Nov 15 '12 at 12:13
    
Very nice. concat() is actaully a standard function and one of the few things that does exist in nearly all DBMS. So it could be used to write "portable" SQL to a certain extent. And the "not null" problem can be handled by using the standard coalesce() function (which is a bit more compact than a CASE) –  a_horse_with_no_name Nov 15 '12 at 12:34
    
@a_h Coalesce? I can't see how that handles the COALESCE(' - ' || p3.name, '') part for Oracle. When p3.name is null, it leaves the unwanted ' - ' –  孔夫子 Nov 15 '12 at 12:38
show 15 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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