🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

How can I return pivot table output in MySQL?

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 a number of hits per company_name. I'm not sure if this is called a pivot table but someone suggested that?
pivot sql mysql
by

2 Answers

aashaykumar 


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
RoliMishra
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

Login / Signup to Answer the Question.

LoginSignup