MySQL Row to column

Recently encountered a demand

The original data is as follows:

Mysql> select Id,sdkname,sid,date,total_count from U1ge_query_log;

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

| ID | Sdkname | Sid | Date | Total_count |

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

| 521 | Icc_iccgame (3) | 11 |           2017-05-01 | 0 |

| 522 | Icc_iccgame (3) | 11 |           2017-05-02 | 0 |

| 523 | Icc_iccgame (3) | 11 |           2017-05-03 | 1 |

| 531 | Icc_huawei | 11 |           2017-05-01 | 0 |

| 532 | Icc_huawei | 11 |           2017-05-02 | 0 |

| 533 | Icc_huawei | 11 |           2017-05-03 | 0 |

| 541 | Icc_iccgame (0) | 11 |           2017-05-01 | 0 |

| 542 | Icc_iccgame (0) | 11 |           2017-05-02 | 0 |

| 543 | Icc_iccgame (0) | 11 |           2017-05-03 | 0 |

| 551 | Icc_uc | 11 |           2017-05-01 | 0 |

| 552 | Icc_uc | 11 |           2017-05-02 | 0 |

| 553 | Icc_uc | 11 |           2017-05-03 | 0 |

| 561 | Icc_qihoo | 11 |           2017-05-01 | 0 |

| 562 | Icc_qihoo | 11 |           2017-05-02 | 0 |

| 563 | Icc_qihoo | 11 |           2017-05-03 | 0 |

| 571 | Icc_vivo | 11 |           2017-05-01 | 0 |

| 572 | Icc_vivo | 11 |           2017-05-02 | 0 |

| 573 | Icc_vivo | 11 |           2017-05-03 | 0 |

| 581 | Icc_gionee | 11 |           2017-05-01 | 0 |

| 582 | Icc_gionee | 11 |           2017-05-02 | 0 |

| 583 | Icc_gionee | 11 |           2017-05-03 | 0 |

Requirements are shown below:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/9A/20/wKioL1lR4wmhCmyLAAAmSfufN0w073.png-wh_500x0-wm_ 3-wmp_4-s_1721489356.png "title=" qq picture 20170627124546.png "alt=" Wkiol1lr4wmhcmylaaamsfufn0w073.png-wh_50 "/>

Row to column is used here, because the column is not fixed, consider using dynamic SQL

SET @d= (select Group_concat (' if (sdkname=\ ', sdkname, ' \ ', total_count,0) as ', Sdkname, ') from (select DISTINCT Sdkname from Pcik_log_dep.u1ge_query_log) A);  SET @sql =concat (' Select Date, ', @d, ' from Pcik_log_dep.u1ge_query_log Group by Date '); PREPARE sdtmt from @sql; EXECUTE Sdtmt;deallocate prepare sdtmt;

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

| Date | Icctest | Icc_baidu | Icc_coolpad | Icc_gionee | Icc_huawei | Icc_iccgame (0) | Icc_iccgame (3) | Icc_lenovo | Icc_meizu | Icc_oppo | Icc_qihoo | Icc_uc | Icc_vivo | Icc_xiaomi |

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

|       2017-05-01 |         0 |           0 |          0 |          0 |              0 |              0 |          0 |         0 |        0 |         0 |      0 |        0 |          0 | 0 |

| 2017-05-02 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |              0 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

| 2017-05-03 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |              1 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

| 2017-05-04 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |           4380 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

| 2017-05-05 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |           5126 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

| 2017-05-06 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |           5571 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

| 2017-05-07 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |           5888 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

| 2017-05-08 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |           6135 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

| 2017-05-09 |       0 |         0 |           0 |   & nbsp      0 |          0 |              0 |           6199 |          0 |         0 |        0 |         0 |      0 |        0 |          0 |

|       2017-05-10 |         0 |           0 |          0 |          0 |              0 |           0 |          6199 |         0 |        0 |         0 |      0 |        0 |          0 | 0 |

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

Rows in Set (0.00 sec)

MySQL Row to column