Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jun 17 '13 at 13:03

This question came from our site for professional system and network administrators.

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.