MySQL query to create a Matrix view using pivot

MySQLpivot

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.

Best Answer

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)

Related Solutions

MySQL Reusing calculation in SELECT clause in a VIEW

You can only do this in a view by repeating the expression.

CREATE VIEW upcoming_events_view AS
SELECT *, 
       TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (TIMESTAMPDIFF(WEEK, `create_date`, NOW()) % week = 0) AS do_current_week
  FROM event_repeats

The NOW() function uses the time at the beginning of statement execution no matter how long the query takes to run, so you'd never get two different answers from NOW() in the same query. The TIMESTAMPDIFF() function's second invocation will probably not be optimized away by the optimizer, but this is a trivial calculation. It benchmarks at 0.5 microseconds on an AMD Opteron 1GHz processor, so a million calls to that function takes half a second.

Since your create_date is a DATE, not a DATETIME, the CURDATE() function might be a more technically accurate choice, since the TIMESTAMPDIFF() function first converts your dates to datetimes with an implicit 00:00:00 time element.

mysql> select TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23'), TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23');
+----------------------------------------+--------------------------------------------+
| TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23') | TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23') |
+----------------------------------------+--------------------------------------------+
|                                      0 |                                          1 |
+----------------------------------------+--------------------------------------------+

Mysql – Pivot table with MySQL

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)

Best Answer

Related Solutions

MySQL Reusing calculation in SELECT clause in a VIEW

Mysql – Pivot table with MySQL

Related Question