MySQL query to create Matrix view using pivot

Question

I have a table with the following fields:

p1
p2
s1
s2

where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The no of players can vary from 3 - 10. The table have the results between them. Let's suppose that we have 4 players: TIM, ROB, NICK, GEORGE and his matches:

    p1     p2       s1  s2
    TIM    ROB      4   0
    NICK   GEORGE   4   0
    TIM    NICK     0   4
    ROB    GEORGE   4   0
    TIM    GEORGE   4   0
    ROB    NICK     4   0

What I want to produce with the query is something like this:

           TIM   ROB   NICK   GEORGE
    TIM     X    4-0   0-4     4-0
    ROB    0-4    X    4-0     4-0
    NICK   0-4   4-0    X      4-0
    GEORGE 0-4   0-4   0-4      X

Is it possible to have the above?

Till now the only thing I have achieved is to have it using GROUP_CONCAT combined with CONCAT using the following query:

    SELECT RT.Player1, GROUP_CONCAT(CONCAT(RT.Player2,':',RT.Result1,'-', RT.Result2) SEPARATOR '\n'))
    FROM 
    (SELECT R.p1 AS Player1, R.p2 AS Player2, R.s1 AS Result1, R.s2 AS Result2 FROM  fab_matches AS R      
    UNION ALL
    SELECT R.p2 AS Player1, R.p1 AS Player2, R.s2 AS Result1, R.s1 AS Result2 FROM  fab_matches AS R) AS RT

However this is not the expected presentation...

Thanks in advance.

I have found a similar question but it's applied on SQL and it's not exactly the same presentation... http://stackoverflow.com/questions/32334751/squash-league-results-sql-query/32340699#32340699 — dimoss, Oct 24 '15 at 14:59
[What have you tried?](http://mattgemmell.com/what-have-you-tried/) — Pred, Oct 27 '15 at 08:44
The only thing I have done and I am stuck because of my knowledge lack is using UNION to have the results vice versa on different rows. — dimoss, Oct 27 '15 at 09:27
I added GROUP_CONCAT combined with CONCAT but again the presentation in not as good as I would like. — dimoss, Oct 27 '15 at 11:21
MySQL has no support for dynamic pivots. Check this thread http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot. And as a note: formatting the output is NOT the database server's job, but the application's. Do the formatting in the presentation layer if it is possible. — Pred, Oct 27 '15 at 11:55
When I say presentation I mean the way the db handles the columns. Of the actual presentation is application job. With GROUP_CONCAT and CONCAT I can get the result I want which is actually to see the results of each player with the rest ones. However I would prefer the matrix presentation..;-) I know it's something that can be done but unfortunately I don't know how. — dimoss, Oct 27 '15 at 14:55

MySQL query to create Matrix view using pivot

0 Answers0