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?


@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:

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


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

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`,
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
    ) AS 'EMAIL',
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
    ) AS 'PRINT 1 pages',
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
    ) AS 'PRINT 2 pages',
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
    ) 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:

@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… or the Internet Wayback Machine (*/‌​tree/queries.php)

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

link is accessible at this url

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

There is another way to generate a pivot table without using "if", "case", or "GROUP_CONCAT":

@Caius Jard 2020-05-10 06:51:09

You can remove the ELSE NULL from your CASE as hat is the default behavior (and conditional aggregation is wordy enough)

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

@Talha 2019-08-30 16:39:18

sorry guys can't remember further details. I do stuff for fun and then forget or destroy the entire project. But when I stumble upon a challenge I share how I fixed it. I know my example is not very detailed but I guess it may give directions to those who know what they are up against :)

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

My solution is in T-SQL without any pivots:

    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

@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()

@kajacx 2020-03-02 08:04:25

I think the CASE is unnesesary in SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END), you can just do SUM(action='PRINT' AND pagecount=1) since the condition will be converted to 1 when true and 0 when false

@Caius Jard 2020-05-10 06:53:56

@kajacx yes, though it's needed on database that don't have that sort of Boolean manipulation. Given a choice between a "longer syntax that works on all dB" and a "shorter syntax that only works on ..." I would pick the former

@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

    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
GROUP BY company_name


@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

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


And COUNT only counts non-null values. Voilá.

@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;
      '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

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

PREPARE stmt FROM @sql;


@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, 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  
(select 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

(SELECT as name, sales.products_id as prodid, count( as counts
FROM test.sales left outer join on sales.partners_id =
left outer join test.products on sales.products_id = 
where sales.partners_id = and sales.products_id = group by, prodid) t2) t3

group by ;

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

11 Answered Questions

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

16 Answered Questions

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

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

1 Answered Questions

[SOLVED] MySQL pivot row into dynamic number of columns

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

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

24 Answered Questions


36 Answered Questions

[SOLVED] How to output MySQL query results in CSV format?

  • 2008-12-10 15:59:51
  • MCS
  • 1203226 View
  • 1183 Score
  • 36 Answer
  • Tags:   mysql csv quotes

23 Answered Questions

[SOLVED] How do I connect to a MySQL Database in Python?

  • 2008-12-16 21:49:09
  • Marc Lincoln
  • 1246178 View
  • 1163 Score
  • 23 Answer
  • Tags:   python mysql

47 Answered Questions

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

15 Answered Questions

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

Sponsored Content