Hello. Suppose there is a table:

 |  id |  item |
 -------------
 |  1 |  egg |

Not big enough, but for example, come down.

There is a second table:

 |  id |  item_id |  shape |  corners |
 ----------------------------------
 |  1 |  1 |  round |  0 |
 |  2 |  1 |  cube |  4 |

I'll be brief - question:
How to get the following table?

 |  id |  item |  round |  cube |
 ----------------------------
 |  1 |  egg |  0 |  4 |

ZY Connection goes by id <-> item_id

  • It is in this form - only through an additional join . But where is the guarantee that there will always be only two fields? You can slightly alter the result - in one column the list of shape , in another column - the list of corners . This can be done via GROUP_CONCAT And you will separate them programmatically. - BOPOH
  • @BOPOH how many joins will load the request? - igolka97
  • one
    Within reasonable limits, nothing terrible will happen. That's just the wrong approach. After all, as I understand it, one item_id will have two entries, the other will have three, and the tenth will have all ten. Although if one item_id guaranteed to have only two entries, then I think you can do it through join . - BOPOH
  • @BOPOH there appears a new problem .. I don’t know how many of these columns will be, for one it will be just round , for the second and round and cube , and for the third there will not be any, and for the fourth there will be 3 columns that are not similar to the previous ones and etc. - igolka97
  • And what prevents the client (in this case, php) to get the necessary fields? I say - you can get a list of corners through GROUP_CONCAT in one field, a list of corners in another field ( example ), and in php from these fields generate the result you need through explode . True, the result here is not always desired . Easier on the client (php) to collect everything - BOPOH

2 answers 2

As @BOPOH says, you can use a JOIN , but the only way to use GROUP_CONCAT :

 SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(t2.shape = ''', shape, ''', t2.corners, NULL)) AS ', shape)) INTO @pivot_sql FROM table2; SET @pivot_sql = CONCAT('SELECT t1.id, t1.item, ', @pivot_sql, ' FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.id = t2.item_id GROUP BY t1.id, t1.item'); PREPARE stmt FROM @pivot_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE As noted in the comments, it is better to collect the necessary request on the client.

  • I have been sitting for half an hour already trying to figure it out ... What will happen in @pivot_sql ? - igolka97
  • @ igolka97 after the first SELECT in the variable there will be a line like: MAX(IF(t2.shape = 'значение 1 поля shape', t2.corners, NULL) as 'значение 1 поля shape', MAX(IF(t2.shape = 'значение 2 поля shape', t2.corners, NULL) as 'значение 2 поля shape', ... , MAX(IF(t2.shape = 'значение N поля shape', t2.corners, NULL) as 'значение N поля shape' After the second SELECT will be a ready-made query. In general, put a SELECT @pivot_SQL after each change and I SELECT @pivot_SQL figure it out. - vikolyada
  • Why then max ()? - igolka97
  • one
    @ igolka97 because, as the second SELECT returns two records in one, the value of shape will be equal to round and not in the second. Accordingly, there will be two values NULL and 0 , and then using the grouping MAX() will return a larger value, namely 0 . - vikolyada
  • one
    What is the point of generating a request on the server and not on the client? Given that exactly which columns will return and what data in them should be sought is not entirely clear. Given that it is necessary to additionally control the data for the shape: injection, the length of the alias name, etc. / This method is original and can even be used somewhere, but certainly not the right one (as opposed to join). - Yura Ivanov

of course you can

 SELECT a1.id, a1.item, (SELECT corners from table2 where item_id = a1.id limit 0, 1) as round, (SELECT corners from table2 where item_id = a1.id limit 1, 1) as cube from( SELECT id, item from table1 ) as a1

So it will be displayed, but why do you need it?

Isn't it better to choose the data as it is and just manipulate it when displaying it? (ie, the data was selected with the help of JOIN and when you process it you enter it into an array and that's it ... you have data ready for work)

On the client, you can also manipulate the data, as described above, but then there’s a question (why choose data from the database that is not needed?), If your data on the client will often need to display some other dynamically, then not to make a selection from the database here and there ... maybe it makes sense to choose a bunch and already on the client to discard it, if such a task does not provide for that, I wouldn’t do it on the client.