mysql date rows to columns then show in weeks

Question

Any help on this is greatly appreciated. I have a table as shown below and i need the contractor name to stay where it is but i need the start date to be a column name and then the area to be shown in the row under that start date.

I then need to be able to show this data in week views from Monday to Friday. If a contractor has no area work then i still need to show his name but just a black row.... Help please Thanks Lee-Anne

Contractor      startdate   Area
BARNES Bill     2016-10-28  Area a
BARNES Bill     2016-11-02  Area a
BARNES Bill     2016-11-07  Area b
BARNES Bill     2016-11-01  Area a
BARNES Bill     2017-01-09  Area a
BARNES Bill     2017-02-17  Area b
BAUME Peter     
BAUME Peter     
BILLINGS Roger      
CAMPBELL Lee    2017-07-03  Area b
CAMPBELL Lee    2017-07-26  Area b
CAMPBELL Lee    2017-07-12  Area b
CAMPBELL Lee    2017-07-14  Area b
CARNE Jim       2017-07-05  Area a
CENTRONE Krista     
COMBES Greg     2017-07-05  Area b
COMBES Greg     2017-07-05  Area b
COMBES Greg     2016-09-09  Area c
DEVRIES Phil    2017-07-19  Area c
DEVRIES Phil    2018-03-26  Area A

Need to view like this

Contractor  2016-11-02  2016-11-07  2016-11-01  2017-01-09  2016-11-07
BARNES Bill   Area a                
BARNES Bill   Area a            
BARNES Bill   Area b            
BARNES Bill   Area a            
BARNES Bill   Area a                
BARNES Bill   Area b                
BAUME Peter                 
BAUME Peter                 
BILLINGS Roger                  
CAMPBELL Lee                Area b  
CAMPBELL Lee                Area b  
CAMPBELL Lee                Area b  
CAMPBELL Lee                Area b  
CARNE Jim   Area a              
CENTRONE Krista                 
COMBES Greg Area b              
COMBES Greg Area b              
COMBES Greg                     Area c
DEVRIES Phil                    Area c
DEVRIES Phil                    Area A

check this answer:https://stackoverflow.com/questions/42408465/need-to-convert-columns-into-rows-in-mysql/42409047#42409047 — chirag satapara, Sep 07 '17 at 13:52

score 0 · Answer 1 · answered Sep 07 '17 at 14:22

You may do a query like

SELECT Contractor,GROUP_CONCAT(Area) as comma_area,DATE_SUB(startdate,INTERVAL WEEKDAY(startdate) DAY ) as week, MONTH(startdate) as montn,YEAR(startdate) FROM table1 GROUP BY WEEK(startdate,1);

It will not give you exact result , But it will give you area in a comma separated field with contractor name, week , month and year. You can modify the result later in the code.

mysql date rows to columns then show in weeks

1 Answers1