Dynamically pivotting table

Question

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

Check if https://stackoverflow.com/a/66136355/10138734 helps. — Akina, Jan 19 '23 at 05:04
not quite, but its pretty dang close. is it possible to print what queries the procedure is doing? there are a couple changes id like to make but its hard to see where to make them — abc123, Jan 19 '23 at 05:55
You may: #1. Convert your query which produces non-pivotted data to view; #2. Edit my SP text, modify ```'SELECT to_json.`'``` literal to ```'CREATE TABLE tmp_table SELECT to_json.`'```. #3. Execute SP once providing your view name to it as source table, SP will create `tmp_table` table with pivotted data only. #4. Create a query which joins created table `tmp_table` with your source data and adds needed columns (also you may reorder columns if needed). Final desired output is formed. #5. Drop `tmp_table`. — Akina, Jan 19 '23 at 08:04
ok yeah, i changed it so instead of taking in a table, it takes a statement instead. works pretty good, thanks again! — abc123, Jan 27 '23 at 01:00

score 0 · Answer 1 · answered Jan 19 '23 at 03:49

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.

score 0 · Answer 2 · answered Jan 19 '23 at 20:55

0

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

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

answered Jan 19 '23 at 20:55

Rick James

135,179
13
127
222

Dynamically pivotting table

2 Answers2