1

I am in the process of planning a database for a mysql project and think I need to get this solved before I go any further. 

CREATE TABLE ResultsTbl ( EventID INTEGER, MatchNumber INTEGER (9), TeamNumber int, IndividualName CHAR (100), Result char (4) ); INSERT INTO ResultsTbl VALUES (1,1,1,'individual 1','W'); INSERT INTO ResultsTbl VALUES (1,1,1,'individual 2','W'); INSERT INTO ResultsTbl VALUES (1,1,2,'individual 3','L'); INSERT INTO ResultsTbl VALUES (1,1,2,'individual 4','L'); INSERT INTO ResultsTbl VALUES (1,1,3,'individual 5','L'); INSERT INTO ResultsTbl VALUES (1,1,3,'individual 6','L'); INSERT INTO ResultsTbl VALUES (1,2,1,'individual 7','W'); INSERT INTO ResultsTbl VALUES (1,2,2,'individual 8','L'); INSERT INTO ResultsTbl VALUES (1,3,1,'individual 9','W'); INSERT INTO ResultsTbl VALUES (1,3,1,'individual 10','W'); INSERT INTO ResultsTbl VALUES (1,3,2,'individual 11','L'); INSERT INTO ResultsTbl VALUES (1,3,2,'individual 12','L');

So I have a table which holds data for matches. I need the end result to have one cell for each MatchNumber where the Result = 'W' and one where the Result = 'L', but I need the IndividualNames on the same teams concatenated with a "&". I also need the teams concatenated with a ",".

For the example above, the desired result needs to be as follows:

MatchNumber | Winners | Losers 1 | Individual 1 & individual 2 | individual 3 & individual 4, individual 5 & individual 6 2 | individual 7 | individual 8 3 | individual 9 & individual 10 | individual 11 & individual 12

So on MatchNumber 1 we have one team in the winners but 2 teams in the losers. The individual within the same teams are concatenated with a "&", and the different teams are concatenated with a ",". Match 2 only has 1 individual on each team, so there are no special characters needed. Match 3 has 1 team of 2 individuals on both the winners and losers, so they are concatenated with a "&" with no need for a ",".

I would love to write what I have failed on but I just can't get any where near what I need to do. I have looked down the road of a group_concat on the teams, then a group_concat on the result, but I can't figure it out.

I wanted to get this dealt with before processing all of the data, so if the table structures need changing to get this done then I am open to suggestions.

Thanks for any help or advice anyone can give.

Improve this question

2 Answers 2

Reset to default
1

You need a first set of results by match number, team, and winning type
You need second set of result by match number and winning type 

 select MatchNumber, group_concat( Winners ) Winners, group_concat( Losers ) Losers from ( select MatchNumber, TeamNumber, case Result when 'W' then group_concat(IndividualName separator '&') end Winners, case Result when 'L' then group_concat(IndividualName separator '&') end Losers, group_concat(Result separator '&') r from Resultstbl group by MatchNumber, TeamNumber ) g group by MatchNumber
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hey, this worked brilliantly. Exactly as I described. Thanks a lot Ravinder.
0

Give this a bash:

select MatchNumber, group_concat(Winners order by TeamNumber), group_concat(Losers order by TeamNumber) from ( select MatchNumber,TeamNumber, group_concat(case when Result = 'W' then IndividualName end SEPARATOR " & ") as Winners, group_concat(case when Result = 'L' then IndividualName end SEPARATOR " & ") as Losers from ResultsTbl group by MatchNumber, TeamNumber ) t group by MatchNumber ;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.