Good day! Please help me transfer the region column from row to col (put the value of forecast and region with the value of quantity), given that there can be any number of regions, I will be very grateful for useful articles. If it is not difficult to comment, since I still have nothing to do.

Request:

SELECT products.name product, products.price, regions.region, SUM(forecasts.quantity) AS quantity, SUM(forecasts.forecast) AS forecast, forecasts.start_date FROM forecasts LEFT JOIN products ON products.id = forecasts.product_id LEFT JOIN regions ON regions.id = forecasts.region_id WHERE forecasts.start_date = "2016-11-14" GROUP BY forecasts.product_id, forecasts.region_id;

Here is the table:
enter image description here

After that, the resulting data is displayed in the following table:

enter image description here

  • Not quite clear. You want where quantity! = 0 in the cell of the region was concatenated region + forecast + quantity? or what? do update or select? .......... столбец region из row перенести в col (поместить в него значение forecast и region со значением quantity) is not clear - Alexey Shimansky
  • SQL pivot. I myself am a front-end and I had to write a project as a whole, this is the last part of my application. Need help with a query that will dynamically add 2 columns to 1 region (the first is a region quantity, the second region is a forecast) and accordingly substitutes the values ​​of quantity and the forecast that correspond to the product and region. Something like that. So far, I can not perform .. In the example | Dnepr forecast | Dnepr quantity | USA forecast | USA quantity | etc. depending on the number of registered regions. Thanks so much for responding! - Vlad
  • | product | price | Dnepr forecast | Dnepr quantity | USA forecast | USA quantity | n quantity | n forecst | date | The result should be a table of this kind, taking into account the fact that regions may be n - Vlad
  • Clear. Clear. Despite this task, I just want to note that this approach is incorrect. Especially if the regions can be dozens. You yourself will be comfortable with a swollen table with a hundred columns? ........... The total amount by region is always calmly chosen by the request and given to the interviewer at any time from a similar table .. You do not need to make such crutches for this. But if you think that this will increase productivity and you really want to, then at least you need to have a separate table with columns id/region/total_forecast/total_quantity and update the table once in N time. - Alexey Shimansky
  • This table is collected by means of SELECT and JOIN, I actually need to collect a request to expand it in the right direction, pick up the data sent to the client and forget) By the number of columns, I mean the number up to 30 pieces, goods 30. For each product, people make a forecast and we we filter it by region, region, seller, customer, company .. If you have a better solution, let's discuss, suddenly it really worked and went into the wrong steppe .. Criticism is good - Vlad

0