How can I return pivot table output in MySQL

Question

If I have a MySQL table looking something like this    company name    action  pagecount ------------------------------- Company A       PRINT   3 Company A       PRINT   2 Company A       PRINT   3 Company B       EMAIL    Company B       PRINT   2 Company B       PRINT   2 Company B       PRINT   1 Company A       PRINT   3   Is it possible to run a MySQL query to get output like this    company name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages ------------------------------------------------------------- CompanyA        0       0               1               3 CompanyB        1       1               2               0   The idea is that pagecount can vary so the output column amount should reflect that  one column for each action pagecount pair and then number of hits per company name  I m not sure if this is called a pivot table but someone suggested that

User · Answer

Correct answer is   select table record id  group concat if value name  note   value text  NULL   as note  group concat if value name  hire date   value text  NULL   as hire date  group concat if value name  termination date   value text  NULL   as termination date  group concat if value name  department   value text  NULL   as department  group concat if value name  reporting to   value text  NULL   as reporting to  group concat if value name  shift start time   value text  NULL   as shift start time  group concat if value name  shift end time   value text  NULL   as shift end time from other value where table name    employee  and is active    y  and is deleted    n  GROUP BY table record id

User · Answer

There is a tool called MySQL Pivot table generator  it can help you create web based pivot table that you can later export to excel if you like   it can work if your data is in a single table or in several tables    All you need to do is to specify the data source of the columns  it supports dynamic columns   rows   the values in the body of the table and table relationship  if there are any     The home page of this tool is http   mysqlpivottable net

User · Answer

A stardard-SQL version using boolean logic   SELECT company name        COUNT action    EMAIL  OR NULL  AS  Email         COUNT action    PRINT  AND pagecount   1 OR NULL  AS  Print 1 pages         COUNT action    PRINT  AND pagecount   2 OR NULL  AS  Print 2 pages         COUNT action    PRINT  AND pagecount   3 OR NULL  AS  Print 3 pages  FROM   tbl GROUP  BY company name    SQL Fiddle   How   TRUE  OR NULL  yields TRUE  FALSE OR NULL yields NULL  NULL  OR NULL yields NULL  And COUNT only counts non-null values  Voil

User · Answer

One option would be combining use of CASE  WHEN statement is redundant within an aggregation for MySQL Database  and considering the needed query generation dynamically along with getting proper column title for the result set as in the following code block   SET  sql   NULL   SELECT GROUP CONCAT               CONCAT  SUM   action         action       pc0     AS   action pc1             INTO  sql   FROM          SELECT DISTINCT  action              IF  pagecount  IS NULL    CONCAT  page   pagecount    AS pc1            IF  pagecount  IS NULL    CONCAT   AND  pagecount       pagecount       AS pc0      FROM  tab       ORDER BY CONCAT action pc0       t   SET  sql   CONCAT  SELECT company name    sql   FROM  tab  GROUP BY company name     SELECT  sql    PREPARE stmt FROM  sql  EXECUTE stmt  DEALLOCATE PREPARE stmt   Demo

User · Answer

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

User · Answer

For dynamic pivot  use GROUP CONCAT with CONCAT  The GROUP CONCAT function concatenates strings from a group into one string with various options   SET  sql   NULL  SELECT     GROUP CONCAT DISTINCT     CONCAT         SUM CASE WHEN action             action     AND                 CASE WHEN pagecount IS NOT NULL             THEN CONCAT  pagecount     pagecount              ELSE pagecount IS NULL END           THEN 1 ELSE 0 end  AS          action  IFNULL pagecount                INTO  sql FROM   t   SET  sql   CONCAT  SELECT company name      sql                       FROM t                     GROUP BY company name     PREPARE stmt FROM  sql  EXECUTE stmt  DEALLOCATE PREPARE stmt    DEMO HERE

User · Answer

My solution is in T-SQL without any pivots   SELECT     CompanyName        SUM CASE WHEN  action  EMAIL   THEN 1 ELSE 0 END  AS Email      SUM CASE WHEN  action  PRINT  AND pagecount 1  THEN 1 ELSE 0 END  AS Print1Pages      SUM CASE WHEN  action  PRINT  AND pagecount 2  THEN 1 ELSE 0 END  AS Print2Pages      SUM CASE WHEN  action  PRINT  AND pagecount 3  THEN 1 ELSE 0 END  AS Print3Pages FROM      Company GROUP BY      CompanyName

User · Answer

For MySQL you can directly put conditions in SUM   function and it will be evaluated as Boolean 0 or 1 and thus you can have your count based on your criteria without using IF CASE statements  SELECT     company name        SUM action    EMAIL  AS Email      SUM action    PRINT  AND pagecount   1 AS Print1Pages      SUM action    PRINT  AND pagecount   2 AS Print2Pages      SUM action    PRINT  AND pagecount   3 AS Print3Pages FROM t GROUP BY company name   DEMO

User · Answer

select t3 name  sum t3 prod A  as Prod A  sum t3 prod B  as Prod B  sum t3 prod C  as    Prod C  sum t3 prod D  as Prod D  sum t3 prod E  as Prod E   from  select t2 name as name   case when t2 prodid   1 then t2 counts else 0 end  prod A    case when t2 prodid   2 then t2 counts else 0 end prod B   case when t2 prodid   3 then t2 counts else 0 end prod C   case when t2 prodid   4 then t2 counts else 0 end prod D    case when t2 prodid    5  then t2 counts else 0 end prod E  from   SELECT partners name as name  sales products id as prodid  count products name  as counts FROM test sales left outer join test partners on sales partners id   partners id left outer join test products on sales products id   products id  where sales partners id   partners id and sales products id   products id group by partners name  prodid  t2  t3  group by t3 name

[mysql] How can I return pivot table output in MySQL?

Examples related to mysql

Examples related to sql

Examples related to pivot