By peku


2011-10-06 13:20:14 8 Comments

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?

8 comments

@Talha 2016-12-18 19:44:08

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

@Patrick Murphy 2018-02-02 03:55:43

Is this just an example you had on hand? What is the structure of the other_value table?

@Raymond Nijland 2019-06-19 11:11:56

"Correct answer is:" Most likely not as it is missing the SET query to increase the defualt value which is limited to 1024 for GROUP_CONCAT after 1024 GROUP_CONCAT simply truncates the string without a error meaning unexpected results can happen..

@Bjoern 2011-10-06 13:43:44

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:

@Bjoern 2013-12-18 13:42:41

@JohnIsaiahCarmona sorry for that, but its not my style to copy another ones research into my answer. I've updated the answer provided in 2011 with my own material specific for this question. If you want to delve deeper, check out the additional links I've provided. Cross your fingers, maybe the site if initially linked to is just down temporarily.

@Lykegenes 2014-06-20 14:25:19

the link seems to work for now... if it ever goes down again, try these : Google's cache webcache.googleusercontent.com/… or the Internet Wayback Machine (web.archive.org/web/20070303120558*/artfulsoftware.com/info‌​tree/queries.php)

@MrPandav 2015-12-29 13:07:24

link is accessible at this url artfulsoftware.com/infotree/qrytip.php?id=78

@user2513149 2016-10-16 16:15:14

There is another way to generate a pivot table without using "if", "case", or "GROUP_CONCAT": en.wikibooks.org/wiki/MySQL/Pivot_table

@Edwin Bermejo 2018-02-15 07:59:19

Hi good day! What if my conditions is not determined / from another table?

@Bjoern 2018-02-16 06:56:40

@Edwin, maybe post a separate SO question with an example.

@Edwin Bermejo 2018-02-18 23:28:19

@Bjoern already posted a quesiombut it was marked duplicate.

@Edwin Bermejo 2018-02-18 23:29:35

@Bjoern take a look here..thanks..stackoverflow.com/questions/48393196/…

@RRM 2013-09-25 16:05:08

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

@cars10m 2013-09-27 16:04:27

This works just as well in MySql! ... and it even rhymes ;-)

@itsols 2014-10-10 03:06:19

This works for me even on PostgreSQL. I prefer this method than using the crosstab extension on Postgres as this is cleaner

@Raymond Nijland 2019-03-25 16:25:38

"My solution is in T-SQL without any pivots:" Not only SQL Server it should work on most database vendors which follows the ANSI SQL standards. Note that SUM() can only work with numeric data if you ned to pivot strings you will have to use MAX()

@M Khalid Junaid 2014-07-25 21:19:59

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

@itsols 2014-10-09 13:37:56

That's a really neat one. Do you know if this is standards compliant across other platforms (like Postgres)?

@M Khalid Junaid 2014-10-09 19:57:10

@itsols No its for only Mysql specific

@Erwin Brandstetter 2014-11-17 09:05:03

@itsols: I added another standard SQL version. Postgres also has a dedicated crosstab() function.

@SBF 2017-01-09 11:10:26

Works also for SQLite

@Peter Green 2014-12-31 05:47:11

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) MySQL Pivot Table

The home page of this tool is http://mysqlpivottable.net

@Erwin Brandstetter 2014-11-17 09:02:35

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á.

@Marcelo Amorim 2015-03-25 04:02:40

This mode has a great performance on large tables

@Pacerier 2015-04-02 10:34:16

@Erwin, But how would you know that there are three columns? What if there's 5? 10? 20?

@Erwin Brandstetter 2015-04-02 10:59:38

@Pacerier: The example in the question seems to suggest that. Either way, SQL demands to know the return type. a completely dynamic query is not possible. If the number of output columns can vary you need two steps: 1st build the query, 2nd: execute it.

@Abhishek Gupta 2014-10-10 10:37:14

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

@Pacerier 2015-04-02 10:33:08

String concatenation's performance is horrible.......

@Abhishek Gupta 2015-08-19 07:18:19

Pacerier, true man but for dynamic pivoting its one of the best approach

@Patrick Murphy 2018-02-02 03:53:56

This works well if you have many values in the "actions" column or expect that list to grow over time, as writing a case statement for each value can be time consuming and hard to keep up to date.

@irba 2013-11-27 15:16:29

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 ;

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] Insert into a MySQL table or update if exists

24 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

16 Answered Questions

[SOLVED] How to get the sizes of the tables of a MySQL database?

  • 2012-03-08 15:30:31
  • JPashs
  • 599070 View
  • 794 Score
  • 16 Answer
  • Tags:   mysql

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

15 Answered Questions

[SOLVED] How to get a list of user accounts using the command line in MySQL?

2 Answered Questions

SQL to prioritize company names

  • 2018-02-26 06:36:37
  • dang
  • 64 View
  • -1 Score
  • 2 Answer
  • Tags:   mysql sql

1 Answered Questions

[SOLVED] MySQL pivot row into dynamic number of columns

  • 2012-08-17 11:06:46
  • FeeJai
  • 82363 View
  • 73 Score
  • 1 Answer
  • Tags:   mysql sql pivot

0 Answered Questions

MySQL Pivot on single table, counting records per week

  • 2016-04-01 11:27:10
  • rene
  • 51 View
  • 2 Score
  • 0 Answer
  • Tags:   mysql sql pivot

2 Answered Questions

[SOLVED] MySQL Join Two Queries Horizontally

  • 2012-04-27 21:19:51
  • Andrew
  • 3902 View
  • 5 Score
  • 2 Answer
  • Tags:   mysql

Sponsored Content