Shulou(Shulou.com)06/01 Report--

The following together to understand how to achieve mysql row transfer, I believe that we will benefit a lot after reading, the text in the essence is not much, hope that how to achieve mysql row transfer column this short content is what you want.

The original data are 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 |

The requirements are shown as follows:

Row transfer columns are used here, and since the columns are 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 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | 2017-05-03 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | 2017-05-04 | 0 | 0 | 0 | 0 | 0 | 0 | 4380 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | 2017-05 | 0 | 0 | 0 | 0 | 0 | 0 | 5126 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | 2017-05-06 | 0 | 0 | 0 | 0 | 0 | 0 | 5571 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | 2017-05-07 | 0 | 0 | 0 | 0 | 0 | 0 | 5888 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | 2017-05-08 | 0 | 0 | 0 | 0 | 0 | 0 | 6135 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | 2017-05-09 | 0 | 0 | 0 | 0 | 0 | 0 | 6199 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

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

+- -- +

10 rows in set (0.00 sec)

After reading this article on how to achieve mysql line conversion, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.