0

This is based off a previous question that I didn't explain properly. So Ive added a more detailed explanation and more images.

I have these 2 tables:

enter image description here

When I type the following code I get the results below which is not a problem.

select customer.travelDate, customer.customer, customer.vehicle,customer.zone, prices.cost FROM customer,prices WHERE
customer.travelDate BETWEEN prices.startDate and prices.endDate 
and customer.vehicle=prices.vehicle 
and customer.zone=prices.zones;

enter image description here

The problem is that the rates table has a lot of repetition and mistakes can be made easily when inputting new data. I want to know how to setup the same table output with a smaller "Rates" table, example below.

enter image description here

This table has the same values but is considerably smaller and easier to read. I know its possible to do it with php using by only having numbers in the "Zone" row and concatenation.

$zoneNo = $row["zone"];
$query='Select ... FROM ... WHERE customer.zones=rates.zone'.$zoneNo;

but is there a way to do it within MySQL

Shadow
  • 33,525
  • 10
  • 51
  • 64
Amgad Awad
  • 11
  • 2
  • This transformation is called pivoting. The answers to the duplicate question show you how to accomplish this transformation in mysql. However, please note, the sql-based solution is not particularly efficient, especially if the number of derived columns is dynamic. Consider using php code to transform your standard tabular database output to the required structure. – Shadow Jul 12 '21 at 09:39
  • does this answer your question? https://stackoverflow.com/questions/3664393/how-to-group-mysql-rows-with-same-column-value-into-one-row – Ashok Kumar Thangaraj Jul 12 '21 at 09:41

0 Answers0