How to Return Pivot Table Output in MySQL

How can I return pivot table output in MySQL?

This basically is a pivot table.

A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

I advise reading this post and adapt this solution to your needs.

Update

After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching for mysql pivot answers in here. It really had a vast amount of information, and I won't put everything from there in here (even more since I just don't want to copy their vast knowledge), but I'll give some advice on how to deal with pivot tables the sql way generally with the example from peku who asked the question in the first place.

Maybe the link comes back soon, I'll keep an eye out for it.

The spreadsheet way...

Many people just use a tool like MSExcel, OpenOffice or other spreadsheet-tools for this purpose. This is a valid solution, just copy the data over there and use the tools the GUI offer to solve this.

But... this wasn't the question, and it might even lead to some disadvantages, like how to get the data into the spreadsheet, problematic scaling and so on.

The SQL way...

Given his table looks something like this:

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

Now look into his/her desired table:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The rows (EMAIL, PRINT x pages) resemble conditions. The main grouping is by company_name.

In order to set up the conditions this rather shouts for using the CASE-statement. In order to group by something, well, use ... GROUP BY.

The basic SQL providing this pivot can look something like this:

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.

This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.

Some additional links about this topic:

• http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
• http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
• http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
• https://codingsight.com/pivot-tables-in-mysql/

Implement Dynamic pivot in MYSQL

I got the answer.

SELECT  id,
         GROUP_CONCAT(
             CASE 
                 WHEN question.question_name = 'Household'
                 THEN answer.text
                 ELSE NULL 
             END
         ) AS Household,
         GROUP_CONCAT(
             CASE 
                 WHEN question.question_name = 'Dependents' 
                 THEN answer.text
                 ELSE NULL 
             END
         ) AS Dependents,
         GROUP_CONCAT(
             CASE 
                 WHEN question.question_name = 'Generation'
                 THEN answer.text
                ELSE NULL 
             END
         ) AS Generation
       
    FROM user_answers
    inner join answer on user_answers.answer_id=answer.answer_id
    inner join question on answer.question_id=question.question_id
    GROUP BY id

Pivot Table MySQL from mysql database

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`;

How to count values ​on a pivot table in MySQL?

You want conditional aggregation here:

SELECT
    u.id_user,
    COUNT(CASE WHEN c.status = 'ABAN'  THEN 1 END) AS ABAN,
    COUNT(CASE WHEN c.status = 'DES'   THEN 1 END) AS DES,
    COUNT(CASE WHEN c.status = 'LET'   THEN 1 END) AS LET,
    COUNT(CASE WHEN c.status = 'NOANS' THEN 1 END) AS NOANS,
    COUNT(CASE WHEN c.status NOT IN ('ABAN', 'DES', 'LET', 'NOAN')
               THEN 1 END) AS OTH
FROM Users u
LEFT JOIN Get_cal c
    ON c.id_user = u.id_user
GROUP BY
    u.id_user
ORDER BY
    u.id_user;

MySQL - Pivot table per day

Find it after some tests:

    select 
    y.yr,
    y.mth,
    d.details,
    sum(case when day(app_date) = 1  then val else 0 end) day_01,
    sum(case when day(app_date) = 2  then val else 0 end) day_02,
    sum(case when day(app_date) = 3  then val else 0 end) day_03,
    ...
    sum(case when day(app_date) = 31 then val else 0 end) day_31,
    sum(case when day(app_date) > 0  then val else 0 end) total
FROM (
    SELECT '101' dorder, 'balance' details UNION ALL 
    SELECT '102' dorder, 'in' details UNION ALL
    SELECT '103' dorder, 'out' details
) d cross join (
    SELECT distinct year(app_date) yr, month(app_date) mth
    FROM tblappointment 
) y    
left join (  
        select app_date, COALESCE(app_price, 0) val, 'balance' details from tblappointment 
    union all
        select app_date, COALESCE(app_price_in, 0) val, 'in' details from tblappointment 
    union all
        select app_date, COALESCE(app_price_out, 0) val, 'out' details from tblappointment 
) t on year(t.app_date) = y.yr and month(t.app_date) = y.mth and t.details = d.details
group by y.yr, y.mth, d.details
order by y.yr desc, y.mth desc, d.dorder;

---

Related Topics