Question 21190 in Mysql Question posted in
The official MySQL documentation can be found here.

Dynamically pivotting table – Mysql

enter image description here

Under spu_pricebucket there are 5 different pricebuckets. I want to change it so each pricebucket will have its unitprice below it. End goal is to turn this table from 6 columns and 10 rows to 9 columns and 2 rows, so the table looks as follows:

stockcode PRICE1 PRICE2 PRICE3 PRICE4 PRICE5 baseuomcode uomdesc convfactor
VMC 100MG 3.00 3.00 3.00 3.00 2.85 TAB TABLET 1.000
VMC 100MG 300.00 300.00 300.00 300.00 285.00 BOX BOX 100.00

I am able to achieve this with the following query:

SELECT spu.spu_stockcode, 
       MAX(CASE WHEN spu.spu_pricebucket='PRICE1' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE1',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE2' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE2',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE3' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE3',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE4' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE4',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE5' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE5', 
       UC.uomc_baseuomcode, 
       UOM.uom_uomdesc, 
       UC.uomc_convfactor
FROM stkm_stockpricesuom spu
left join stkm_uomconversion UC on UC.uomc_stockcode = spu.spu_stockcode
Left join stkm_stockuom UOM on UOM.UOM_UOMCODE = UC.uomc_baseuomcode
Where spu.spu_stockcode = 'VMC 100MG'
group by UOM.uom_uomdesc;

Here is the resulting table:

enter image description here

Main problem: If user decides to add (or delete) pricebuckets, the query won’t be able to show that (currently explicitly defining columns PRICE1-5). how can i change it so the query can run regardless of the number of pricebuckets the user may create( or delete)?

Tags:

2

Answers


  1. 0

    Sorry, it’s not possible in SQL.

    An SQL query needs to have all the columns of the select-list fixed at the time the query is prepared. It can’t expand with more columns depending on the values it reads during execution.

    The other option is not to do a pivot table query at all. Just read the data as it is stored in the database. Then write client code to fetch it row by row and pivot it into a table in the presentation. That at least allows the output to expand dynamically without changing the code.

    Login or Signup to reply.
  2. 0

    The following has a Stored Procedure that will dynamically construct and perform the generated SELECT:

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search