liamw Posted November 20, 2008 Share Posted November 20, 2008 I am new to MYSQL, I only started writing SQL queries yesterday. I have previously been working with Access and Excel at work to generate reports but want to try and create a web based solution for managers to view technicians rotas. I have simplified the structure of the tables to make it easier to explain, below I have outlined the contents of two tables one with the techs details and the other with the techs rota in it. The actual tables contain closer to a years worth of rota data. tbl_techs TechID Name ------ ---- 1234 Dave 1235 Sam tbl_rota TechID Date AM PM ------ ---- -- -- 1234 01/01/01 7 5 1234 02/01/01 7 5 1234 03/01/01 0 0 1234 04/01/01 7 5 1234 05/01/01 7 5 1234 06/01/01 7 5 1235 01/01/01 6 5 1235 02/01/01 6 5 1235 03/01/01 0 0 1235 04/01/01 6 5 1235 05/01/01 6 5 1235 06/01/01 6 5 What I want to achieve is something I would have done with a pivot table in Excel, so the data is output somewhat like the below, with dates going across left to right and the details of the techs shift shown one per row. I don't really know where to start I have searched for pivot tables, tranpose and cross tab from mysql but none of the solutions really seem to relate to what I am trying to achieve, any help would be greatly appreciated. Show data in format 01/01/01 02/01/01 03/01/01 04/01/01 am pm am pm am pm am pm 1234 7 5 7 5 0 0 7 5 1235 6 5 6 5 6 5 6 5 Quote Link to comment Share on other sites More sharing options...
fenway Posted November 20, 2008 Share Posted November 20, 2008 I would say that it's easier to aggregate these data into a hash and then handle it in the application... otherwise, it gets a bit ugly query-wise, though definitely possible. Quote Link to comment Share on other sites More sharing options...
liamw Posted November 21, 2008 Author Share Posted November 21, 2008 Could you point me in the direction of how this might be achieved, I am using PHP though I have very basic knowledge of it. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 21, 2008 Share Posted November 21, 2008 Could you point me in the direction of how this might be achieved, I am using PHP though I have very basic knowledge of it. Sadly, I have even more basic knowledge... I could tell you how to do it in Perl ;-) Basically, just get back all of the rows "as-is", then build a hash of ( id, date, times ). Quote Link to comment 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.