Hello. Need help creating a request. The essence of the problem is as follows. There is a table:

|-----------------------------------------------| |complekt_id | main_product | additional_product| |-----------------------------------------------| | 1 | 111 | 222 | |-----------------------------------------------| | 1 | 111 | 333 | |-----------------------------------------------| | 2 | 111 | 444 | |-----------------------------------------------|

The output should be:

 |-----------------------------------------------------------------------| |complekt_id | main_product | additional_product1 | additional_product2 | |-----------------------------------------------------------------------| | 1 | 111 | 222 | 333 | |-----------------------------------------------------------------------| | 2 | 111 | 444 | | |-----------------------------------------------------------------------|
  • Sorry, I'll fix the table view now - Kernel Panic
  • one
    May be separated by commas. - Saidolim
  • one
    Separate columns will not work because the number of columns must be exactly known at the time of compiling the query. only a comma or another separator in one field - Mike
  • one
    ru.stackoverflow.com/questions/520412/… - Mike
  • The number of columns is known. additional_product can be only 2 for identical complekt_id and main_product - Kernel Panic

2 answers 2

With the condition that there can be only 2 entries (indicated in the comments) you can do this:

 select complekt_id, main_product, min(additional_product) as additional_product1, if(count(1)=1, NULL, max(additional_product)) as additional_product2 from Table group by complekt_id, main_product
  • Thank. The most suitable solution! - Kernel Panic
 select t1.complekt_id , t1.additional_product additional_product1 , t2.additional_product additional_product2 from tab t1 left join tab t2 on t1.complekt_id = t2.complekt_id

If you need something dynamic, you can organize in a loop by the number of UNPIVOT fields.

  • If through join you need to modify the conditions. Your current query returns 4 lines with complect_id = 1 and such add1, add2: (222,222), (222,333), (333,222), (333,333) - Mike
  • Yes, conditions are needed - BigDaddy
  • Firstly, you need a condition in the connection that t1.add <t2.add (so that you don’t stick the record to yourself), and secondly, you need to somehow limit the number of lines in the output, because it will still select 2 lines from t1 and will give them both, and only one is needed. then you need to select only those lines for which there are no lines with smaller add - Mike