8

I have a table with the following fields:

p1
p2
s1
s2

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

    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 somehow in MySQL? Thanks in advance.

2

1 Answer 1

0

Example pivot tracking table

CREATE TABLE IF NOT EXISTS `mail_track` (
`ID` int(10) NOT NULL auto_increment,
 MemberID mediumint(7) default NULL,
`MessageTitle` varchar(20) default NULL,
`Type` varchar(6) default NULL,
`URLID` varchar(15) default NULL,
`DateTime` datetime default NULL,
`IP` varchar(15) default NULL,
PRIMARY KEY  (`ID`),
KEY `DateTime` (`DateTime`,`Type`,`MessageTitle`,`MemberID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=199952834 ;

pivot query

SELECT 
SUM( IF( mail_track.Type = 'Open', 1, 0 ) ) AS Opens,
SUM( IF( mail_track.Type = 'Click', 1, 0 ) ) AS Clicks,
SUM( IF( mail_track.Type = 'Sent', 1, 0 ) ) AS Sent,
DATE(DateTime)
FROM mail_track GROUP BY DATE(DateTime) 

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.