How to create a table in MySQL with automatic numeration of columns?

Question

Example. My data is from Table name:BIKE

Bike_No   Repair_Date     Repair_Cost
--------  -----------     ------------
ABC1234   2013-01-05      50.00
BMX5678   2013-02-04      75.00
ABC1234   2013-01-25      20.00
BON3333   2013-03-06      80.00
DEB1111   2013-08-04      40.00
ABC1234   2013-09-06      50.00

I want to see the repair cost for each bike based on a date range (example: from 01/01/2013 to 31/03/2013) and result table must display the repair cost with Bike_No and the affected months as columns. How do we generate the affected months as columns based on the queried date range?

Answer 1

Unfortunately, MySQL does not have a PIVOT function and it sounds like you are attempting to get the data into the format:

+---------+-----------------+-----------------+-----------------+
| bike_no | repair_cost_Jan | repair_cost_Feb | repair_cost_Mar |
+---------+-----------------+-----------------+-----------------+
| ABC1234 |              70 |               0 |               0 |
| BMX5678 |               0 |              75 |               0 |
| BON3333 |               0 |               0 |              80 |
+---------+-----------------+-----------------+-----------------+

If that is the case, then in MySQL you will want to use an aggregate function along with a CASE expression to convert your rows of data into columns.

If you have a know number of months that you are returning then you can easily hard-code the query similar to the following:

select bike_no,
  sum(case when month(repair_date) = 1 then repair_cost else 0 end) repair_cost_Jan,
  sum(case when month(repair_date) = 2 then repair_cost else 0 end) repair_cost_Feb,
  sum(case when month(repair_date) = 3 then repair_cost else 0 end) repair_cost_Mar
from bike
where repair_date >= '2013-01-01'
  and repair_date <= '2013-03-31'
group by bike_no;

See SQL Fiddle with Demo.

But in your case it sounds like you want to change the start date and end date to your query. If that is the case, then you will want to use a prepared statement to generate dynamic SQL that would be executed:

SET @sql = NULL;
set @startdate = '2013-01-01';
set @enddate = '2013-03-31';
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN month(repair_date) = ',
      month(repair_date),
      ' THEN repair_cost else 0 END) AS `repair_cost_',
      monthname(repair_date), '`'
    )
  ) INTO @sql
FROM bike
where repair_date >= @startdate
 and repair_date <= @enddate;

SET @sql 
  = CONCAT('SELECT bike_no, ', @sql, ' 
            from bike
            where repair_date >= ''',  @startdate, ''' 
               and repair_date <= ''', @enddate, ''' 
            group by bike_no');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both queries will generate the table of data above.