Multi Column cross tabulation AKA pivot

Question

I've been learning and practicing sql for about 6 months. I'm ready to throw in and just admit I'm to dumb for it and my brain just can't wrap around most its patterns. I'm trying to create a pivot table. There isn't much in the way simple literature on the subject and the one source I did find talked of creating cubes and basic entity-attribute-value (EAV) logic.. Whatever that means.

name        action  pagecount
-------------------------------
Company A   PRINT    3
Company A   PRINT    2
Company A   PRINT    3
Company B   EMAIL    6
Company B   PRINT    2
Company B   PRINT    2
Company B   PRINT    1
Company A   PRINT    3

I want to cross tabulate (is that the right term?) column name with the total count of column action and the total count of column pagecount

 name        action_PRINT   action_EMAIL       pagecount_PRINT      pagecount_EMAIL
--------------------------------------------------------------------------------
Company A   4                0                 11                       0
Company B   3                1                 5                        6

Exact duplicate: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Kamil, Dec 04 '12 at 08:23
No not really. While searching for an answer I found his question and I copied and pasted his table to use for my question. A little trigger happy there I think :) — , Dec 04 '12 at 11:48

score 2 · Accepted Answer · answered Dec 04 '12 at 10:23

This type of operation is known as a PIVOT but MySQL does not have a pivot function so you will need to replicate it using both an aggregate function and a CASE statement.

You can use the following to produce the result:

select name,
  sum(case when action = 'PRINT' then 1 else 0 end) action_print,
  sum(case when action = 'EMAIL' then 1 else 0 end) action_email,
  sum(case when action = 'PRINT' then pagecount else 0 end) pagecount_print,
  sum(case when action = 'EMAIL' then pagecount else 0 end) pagecount_email
from yourtable
group by name

See SQL Fiddle with Demo

The result would be:

|      NAME | ACTION_PRINT | ACTION_EMAIL | PAGECOUNT_PRINT | PAGECOUNT_EMAIL |
-------------------------------------------------------------------------------
| Company A |            4 |            0 |              11 |               0 |
| Company B |            3 |            1 |               5 |               6 |

Perfect. So case acts sort of pragmatically? It will loop through the case for each row and either do this or that? Thanks! — , Dec 04 '12 at 11:51

Multi Column cross tabulation AKA pivot

1 Answers1