Pivot Table MySQL from mysql database

Question

I would like to make a pivot table that looks like the figure below from a mysql table:

Year                        | 2018  | 2018 |   2018 |  2019   ....
---------------------------------------------------------------
Month                       |  Jan  |  Feb |   Mar  |  Apr   ....
----------------------------------------------------------------
Dolutegravir (DTG) 50mg Tabs| 10000 | 20000|   xx   |  xx    ....
-----------------------------------------------------------------
 xxxxxxxx                   |   xx  |  xx  | xxx    |  xx  .......
-------------------------------------------------------------------

MySql schema and data can be found here http://sqlfiddle.com/#!9/678546/2

Your assistance is appreciated in advance

What query have you tried so far? https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql has a working example that should help. — WOUNDEDStevenJones, Mar 31 '20 at 20:34
Also the table you listed above as an example isn't a valid MySQL result - one that has one header defining the columns, and then rows underneath with the results and values for each column. Please edit that to be in a standard format so it's more apparent what type of output you're looking for. — WOUNDEDStevenJones, Mar 31 '20 at 20:40
Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — nbk, Mar 31 '20 at 23:08

score 1 · Accepted Answer · answered Mar 31 '20 at 20:45

Here's a partial example based on this question. The basic format is for each column you want in the end, you need to define another SUM(CASE(x)). This example currently outputs only 4 months, but you can build it out to include whichever months you need.

http://sqlfiddle.com/#!9/678546/9 for a working example.

SELECT  P.`drug`,
    SUM(
        CASE 
            WHEN P.`data_month`='Jan' AND P.`data_year` = 2018
            THEN P.`dispensed_packs`
            ELSE 0 
        END
    ) AS '2018-01',
    SUM(
        CASE 
            WHEN P.`data_month`='Feb' AND P.`data_year` = 2018
            THEN P.`dispensed_packs`
            ELSE 0 
        END
    ) AS '2018-02',
    SUM(
        CASE 
            WHEN P.`data_month`='Mar' AND P.`data_year` = 2018
            THEN P.`dispensed_packs`
            ELSE 0 
        END
    ) AS '2018-03',
    SUM(
        CASE 
            WHEN P.`data_month`='Apr' AND P.`data_year` = 2018
            THEN P.`dispensed_packs`
            ELSE 0 
        END
    ) AS '2019-01'
FROM    tmp_pivot_dtg P
GROUP BY P.`drug`;

Pivot Table MySQL from mysql database

1 Answers1