Pivoting in mysql based on dynamic values

Question

I have been through many other post, related to my issue but did not find any solution, so please don't mention any other post.

I am using mysql. I am having two tables with the following structure

Items:

 ___________________________________________
| ITEM ID    |  TITLE  | PRICE  | QUANTITY |
|__________________________________________|
|112233      |ABCDE    |  45.00  |  7      |
|223344      |SFDFS    |  34.00  |  8      |    
|334455      |SDFSDF   |  30.00  |  8      |
|787878      |XVXCVX   |  23.45  |  6      |
|888888      |SFSDF    |  29.00  |  5      |
|            |         |         |         |
|____________|_________|_________|_________|

Itemspecifics:

 _______________________________________________
| ITEM ID    |  SPECIFIC NAME  | SPECIFIC VALUE |
|_______________________________________________|
|112233      | STYLE           |  standard      |
|112233      | BRAND           |  phonix        |    
|112233      | COLOR           |  white         |
|112233      | MFG             |  xyz           |
|112233      | YEAR            |  2021          |
|444444      | COLOR           |  black         |
|444444      | MFG             |  abc           |
|444444      | YEAR            |  2020          |
|____________|_________________|________________|

I want result something like following Result:

 ___________________________________________________________________________________
| ITEM ID    |  TITLE  | PRICE   | QUANTITY|  STYLE  |  BRAND  |COLOR | MFG |YEAR   |
|__________________________________________|_________|_________|______|_____|_______|
|112233      |ABCDE    |  45.00  |  7      | standard|  phonix |white | xyz |2021   |
|444444      |SFDFS    |  34.00  |  8      |         |         |black | abc |2020   |
|____________|_________|_________|_________|_________|_________|______|_____|_______|

Specific Name and Specific Values in Itemsspecific table are dynamic and unknown.

Note: I can save data in other structure too if that is easier than the current structure.

Use https://stackoverflow.com/a/66136355/10138734 as a base, and pivot 2nd table into temporary table. Then join 1st table. — Akina, Mar 23 '21 at 10:25
Why ITEM ID = 223344 from 1st table was altered to 444444 in the output? — Akina, Mar 23 '21 at 10:26
@Akina It was mistakenly written just in example. let me correct. — Mehboob Khan Afridi, Mar 23 '21 at 12:59

score 0 · Answer 1 · answered Mar 23 '21 at 17:13

0

For dynamic, construct, then execute, the query. Here is a stored proc to do such:

http://mysql.rjweb.org/doc.php/pivot

answered Mar 23 '21 at 17:13

Rick James

135,179
13
127
222

Pivoting in mysql based on dynamic values

1 Answers1