mysql query:change table to another manifestations

Question

my query:

select Fimp_date, Fadpos, sum(Fplay_num) as Fplay_num from tbl_cnt_display_num_by_adpos where Fimp_date between date_format(now(),'%Y%m%d')-interval 2 day and date_format(now(),'%Y%m%d') group by Fadpos, Fimp_date;

and this is the result： +-----------+--------+-----------+

| Fimp_date | Fadpos | Fplay_num |

+-----------+--------+-----------+

| 20151020 | 0 | 1562156 |

| 20151020 | 1 | 19354 |

| 20151020 | 101 | 352796 |

| 20151020 | 102 | 232052 |

| 20151020 | 103 | 42324 |

| 20151020 | 104 | 214 |

| 20151020 | 105 | 167904 |

| 20151020 | 500 | 49 |

+-----------+--------+-----------+

how to write the sql let the result transform to:

it means that every day as a row, the diffrent Fadpos as diffrent columns and the value is the Fplay_num of the Fadpos on that day.

so how to write the sql?

Possible duplicate of [mysql pivot/crosstab query](http://stackoverflow.com/questions/12382771/mysql-pivot-crosstab-query) — barry-johnson, Oct 21 '15 at 04:48

score 0 · Answer 1 · edited May 23 '17 at 11:58

Try the following...

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(CASE WHEN `Fadpos` = ''',
      `Fadpos`,
      ''' THEN Fplay_num END) AS `',
      `Fadpos`, '`'
    )
  ) INTO @sql
FROM (SELECT Fadpos,
             SUM( Fplay_num ) AS Fplay_num
      FROM tbl_cnt_display_num_by_adpos
      WHERE Fimp_date BETWEEN DATE_FORMAT( NOW(), '%Y%m%d' ) - INTERVAL 2 DAY
                          AND DATE_FORMAT( NOW(), '%Y%m%d' )
      GROUP BY Fadpos,
               Fimp_date) AS AliasForSelect;

SET @sql = CONCAT( 'SELECT Fimp_date,
                           ',
                           @sql,
                           '
                    FROM tbl_cnt_display_num_by_adpos
                    GROUP BY Fimp_date;');

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

Please see my answer at How to match column names and values from joining and related tables? for more information.

Please note that I have been unable to test this code as I would need to create a sample table and populate it with data to do so. If you have script that will do just that then please modify your question. Failing that, editing your question to include a text copy of your output rather than the current image would be helpful.

Good luck!

the query failed,the table struct query is:CREATE TABLE `tbl_cnt_display_num_by_adpos` ( `Fimp_date` int(11) DEFAULT NULL, `Fprograme_id` int(11) DEFAULT NULL, `Fadpos` int(11) DEFAULT NULL, `Fplay_num` int(11) DEFAULT NULL, `etl_stamp` varchar(200) DEFAULT NULL, KEY `Fprograme_id_index` (`Fprograme_id`), KEY `Fimp_date_index` (`Fimp_date`), KEY `index_Fadpos` (`Fadpos`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 — 周川添, Oct 21 '15 at 06:36
To clarify, do Fprograme_id, Fimp_date and Fadpos form a composite primary key, or does just one serve as the primary key? — toonice, Oct 21 '15 at 08:27

mysql query:change table to another manifestations

1 Answers1