MySQL pivot table -
if have mysql table looking this:
company_name action pagecount ------------------------------- company print 3 company print 2 company print 3 company b email company b print 2 company b print 2 company b print 1 company print 3
is possible run mysql query output 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 pagecount can vary output column amount should reflect that, 1 column each action/pagecount pair , number of hits per company_name. i'm not sure if called pivot table suggested that?
this is pivot table.
a nice tutorial on how achieve can found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
i advise reading post , adapt solution needs.
update
after link above not available longer feel obliged provide additional information of searching mysql pivot answers in here. had vast amount of information, , won't put there in here (even more since don't want copy vast knowledge), i'll give advice on how deal pivot tables sql way example peku asked question in first place.
maybe link comes soon, i'll keep eye out it.
the spreadsheet way...
many people use tool msexcel, openoffice or other spreadsheet-tools purpose. valid solution, copy data on there , use tools gui offer solve this.
but... wasn't question, , might lead disadvantages, how data spreadsheet, problematic scaling , on.
the sql way...
given table looks 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 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. main grouping company_name.
in order set conditions rather shouts using case-statement. in order group something, well, use ... group by.
the basic sql providing pivot can this:
select p.`company_name`, count( case when p.`action`='email' 1 else null end ) 'email', count( case when p.`action`='print' , p.`pagecount` = '1' p.`pagecount` else null end ) 'print 1 pages', count( case when p.`action`='print' , p.`pagecount` = '2' p.`pagecount` else null end ) 'print 2 pages', count( case when p.`action`='print' , p.`pagecount` = '3' p.`pagecount` else null end ) 'print 3 pages' test_pivot p group p.`company_name`; this should provide desired result fast. major downside approach, more rows want in pivot table, more conditions need define in sql statement.
this can dealt with, too, therefore people tend use prepared statements, routines, counters , such.
some additional links topic:
Comments
Post a Comment