#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Smile 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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    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
    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
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    please confirm that you want a sql server solution and not a mysql solution (the forum you posted in)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo