How can I separate row values into different columns in PHP and MySQL

Question

I'm trying to create a query that takes values from different rows and put them into different columns. Here is the query I've come up with so far:

SELECT red.match_number AS 'Match #', red.redTeams AS 'Red Alliance', blue.blueTeams AS 'Blue Alliance', red.redScore AS 'Red Alliance Score', blue.blueScore AS 'Blue Alliance Score'
FROM(SELECT match_number, GROUP_CONCAT(team SEPARATOR ' | ') AS redTeams, score AS redScore
FROM `scout_data`
WHERE alliance_color = 'red'
GROUP BY match_number) AS red
LEFT JOIN (SELECT match_number, GROUP_CONCAT(team SEPARATOR ' | ') AS blueTeams, score AS blueScore
FROM `scout_data`
WHERE alliance_color = 'blue'
GROUP BY match_number) AS blue ON red.match_number = blue.match_number

Which creates a table like this:
But I want to separate the numbers in the blue and red alliance columns so it looks something like this:
Except without the qualifications column name. The structure of my table looks like this:
I've limited all the columns in the picture to just what's relevant to the query.

Answer 1

I think what you are wanting or referring to is called a cross join, or a pivot table

As I said in the comments,

Already Answered MySql CROSS JOIN between two tables and match with another

Example from that answer http://sqlfiddle.com/#!9/3540f/4

Warning about MySql's GROUP_CONCAT():

Another thing I should mention ( I feel ) as I see you using GROUP_CONCAT, when I first saw this I thought it was a magical answer for some things I needed. Latter I discovered that it's MySql specific. But more worrisome, and after some hard fought debugging, I discovered that there is a length limit imposed on it. So in short it's possible GROUP_CONCAT can truncate your data. I've decided to avoid using it in my code. Just thought I would warn you of that.

Mysql truncates concatenated result of a GROUP_CONCAT Function

Need more information Because, all that aside this is more of a presentation question, if you have the setup in the first image.

http://puu.sh/jd3a7/658df8a999.png

Already, it would be more appropriate to give us your presentation code ( php\html ) and the data that goes into that then the Sql, the Sql is nice but without the database setup. It's really hard to see what that outputs.

Basically what is the structure of your result array, and how are you getting it to look like image1?

Answer 2

As ArtisiticPhoenix said, I think more information on how the database is configured would be needed in order to provide the most accurate answer. From what I can gather, it looks like all this data is in one single table. The part that I'm confused about is where the score comes from if you're grouping and concatenating by team IDs, is the same score stored for every team? If not then wouldn't that score that's being returned possibly be incorrect?

I guess what I'm getting at is that there appears to be some data normalization issues here, getting some more information would help to debug that as well.