raytri Posted June 7, 2011 Share Posted June 7, 2011 I've been reading up on ways to turn columns into rows, but I'm just not able to turn it into working code. I have a table with data that looks like this: Account Month Transactions 100 May 2009 200 100 June 2009 180 101 May 2009 50 101 June 2009 80 101 July 2009 68 102 June 2009 400 Yes, I know this should be broken up into related tables. But this is what I have to work with. I want to pull a report that displays the data so there is one line per account, and a column for each distinct month in the database, like so: Account May 2009 June 2009 July 2009 100 200 180 0 101 50 80 68 102 0 400 0 There are a lot of months, so I'd like the query to identify each distinct month, rather than me hardcoding it into the query. I've looked at pivot tables, inner joins, etc., but I'm just not grokking what I need to do to break out the months into columns. Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/ Share on other sites More sharing options...
The Little Guy Posted June 8, 2011 Share Posted June 8, 2011 You have the data that you need, pull it out of the database using a simple select, and use php to display it the way you want it. Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1226959 Share on other sites More sharing options...
raytri Posted June 8, 2011 Author Share Posted June 8, 2011 Good suggestion, but I'm just trying to pull a report. I don't want to run it through PHP. Plus I want to learn how to do this in MySQL. Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1226968 Share on other sites More sharing options...
mikosiko Posted June 8, 2011 Share Posted June 8, 2011 old... but good source to solve your issue. http://dev.mysql.com/tech-resources/articles/wizard/page5.html (read pages 4 and 5) Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1227052 Share on other sites More sharing options...
raytri Posted June 8, 2011 Author Share Posted June 8, 2011 Wow. What a great link! I think that solves my problem.... I'll let you know when I try to adapt it to my situation. Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1227066 Share on other sites More sharing options...
raytri Posted June 9, 2011 Author Share Posted June 9, 2011 Hmm. The link helped me get the query to work, but only by manually plugging in a SUM(IF()) statement for each month: SELECT Account, SUM(IF(`Period Ending`='2009-12-31',`No_ Trans`,0)) As Dec09, SUM(IF(`Period Ending`='2010-01-31',`No_ Trans`,0)) As Jan10, SUM(IF(`Period Ending`='2010-03-31',`No_ Trans`,0)) As Mar10 FROM Merchant GROUP BY Account There are quite a few months, with another being added every month. So is there a way to have the query do this dynamically? (create a column for each month in the table, and have each row show the number of transactions for that account in each month). Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1227267 Share on other sites More sharing options...
mikosiko Posted June 9, 2011 Share Posted June 9, 2011 So is there a way to have the query do this dynamically? (create a column for each month in the table... that is why I pointed you first to page 5.... there is an example showing how to do that... the example use Perl... but the important thing are the concepts about how to build the query dynamically ... page 4 shows how to use CONCAT to build part of the query.... you can develop the same concept in PHP or in whatever you feel comfortable. Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1227270 Share on other sites More sharing options...
raytri Posted June 9, 2011 Author Share Posted June 9, 2011 I tried the CONCAT approach, but couldn't get it to work. Again, I'm trying to do this totally in MySQL, without reference to PHP or anything like that. I want to be able to do this in direct queries against the database, not have to run it through a server-side script. Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1227405 Share on other sites More sharing options...
mikosiko Posted June 9, 2011 Share Posted June 9, 2011 the fact that the final query has a variable (dynamic) number of fields to be selected (the sum by month) make no possible (imho) to build it using only direct queries... you must build the final query in some way... either manually or with some kind of programming... other alternatives to do this using only Mysql resources is use Stored Procedures or Functions Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1227437 Share on other sites More sharing options...
raytri Posted June 9, 2011 Author Share Posted June 9, 2011 I was afraid of that. I'm going to mess around with some other ideas, but if I have to enter the months manually, I will. Thanks for all your help! Quote Link to comment https://forums.phpfreaks.com/topic/238610-pivot-tableinner-join/#findComment-1227507 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.