Dynamically pivotting table-CodePudding

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:

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)?

CodePudding user response：

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.

CodePudding user response：

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

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