June 17th, 2013, 09:10 AM
How to create a table in MySQL with automatic numeration of columns?
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
Query is 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 automatically generate the affected months as columns based on the queried date range?
June 17th, 2013, 09:42 AM
thius concept is called a pivot query or pivot report
it is clumsy and inefficient if attempted in mysql (ms access and sql server can do it, though)
better to do it in the application layer anyway
June 17th, 2013, 12:42 PM
Ok, thank you r937. As you replied it can be done in sql server, can you kindly reply some code for my understanding.
Originally Posted by r937
June 17th, 2013, 03:00 PM
please confirm that you want a sql server solution and not a mysql solution (the forum you posted in)