How to write mysql for crosstab report?

Question

I have 3 tables 'media','zone' and 'mediaplaycount' want to write query to fetch data to represent it in crosstab format.

media
medId     medname
-----     -------
1         media1
2         media2
3         media3
-         -
-         -
n         xyz


zone
znId      znName
-----     ------
1         east
2         west
3         south
4         north
-         -
-         -
n         xyz

mediaplaycount 
medId      znId      playdate
------     -----     --------
1          2          1/12/2013
1          1          1/12/2013
3          3          1/12/2013
4          1          1/12/2013

what i want is mediaplacount for each media and zone combination. horizontal field will be all the zone and vertical will be media. result should have count of media against zone and look like

         east    west     south     north    -----   ------   n
media1   1        1       0         0         0        0      0
media2   0        0       0         0         0        0      0
media3   0        0       1         0         0        0      0
media4   1        0       0         0         0        0      0
-----
n

http://stackoverflow.com/questions/8920626/is-it-possible-to-use-crosstab-pivot-query-in-mysql — Deepanshu Goyal, Apr 10 '13 at 11:27

score 0 · Answer 1 · answered Apr 10 '13 at 11:30

The better solution for that is to create Dynamic SQL since there are unknown numbers of znName

SET @sql = NULL;
SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN c.znName = ''',
               znName,
               ''' THEN 1 ELSE 0 END) AS ',
               CONCAT('`', znName, '`')
               )) INTO @sql
FROM `Zone`;

SET @sql = CONCAT(' SELECT  b.medName, ', @sql, ' 
                    FROM    media b
                            LEFT JOIN MediaPlayCount a
                                ON a.medID = b.medID
                            LEFT JOIN Zone c
                                ON a.znID = c.znID
                    GROUP   BY b.MedName');

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

How to write mysql for crosstab report?

1 Answers1