Rotating MySQL table Rows to Columns

Question

I am having an issue even trying to get the idea how this can be done. Please help me if you can or suggest another way of doing it

I am using Jquery Datatables plugin to populate the table. I have a table for Contract of a product called contracts .A contract can have up to 20 products in it and each product is linked to 20 different BUYERS, which means there are around 400 rows of data for each CONTRACT. At some point I will have to print this information (as required). I can print it but is is not reader friendly. So, I want to transform this table in more reader friendly way.

For example, if you see in the picture, I have three products and each product is linked with 3 Buyers . Packing Quantity refers to different types of packings it comes with. In total there are 9 rows (practically I will have min 200 rows). Can this be transformed into the table given below it? which makes it easy to read and print.

Thank you. Any help will be appreciated.

If I was going there; I wouldn't start from here. A database table is not a spreadsheet. — Strawberry, Dec 04 '20 at 21:39
Can you add your current MySQL query, table schema, and example data to your question? You're essentially looking to `GROUP BY BUYER` and then sum over various cases. — WOUNDEDStevenJones, Dec 04 '20 at 21:40
See if this could help https://stackoverflow.com/a/65064507/10910692 — FanoFN, Dec 05 '20 at 03:53

score 3 · Accepted Answer · answered Dec 04 '20 at 21:40

3

This looks like conditional aggregation:

select buyer,
    sum(case when p_name = 'Eggs'   then quantity_type_1 + quantity_type_2 + quantity_type_3 + quantity_type_4 else 0 end) as egg,
    sum(case when p_name = 'Potato' then quantity_type_1 + quantity_type_2 + quantity_type_3 + quantity_type_4 else 0 end) as potato,
    sum(case when p_name = 'Milk'   then quantity_type_1 + quantity_type_2 + quantity_type_3 + quantity_type_4 else 0 end) as milk
from mytable
group by buyer

answered Dec 04 '20 at 21:40

GMB

216,147
25
84
135

thank you. This gave me an idea. However, is it possible to give product names dynamically? Thank you – KT2436 Dec 04 '20 at 22:42
@KT2436: dynamic pivot is much more complicated. A SQL query must return a fixed set of columns, so it cannot be done in pure SQL. You can search the site for "MySQL dynamic pivot" to see what the code would look like. – GMB Dec 04 '20 at 22:53

Rotating MySQL table Rows to Columns

1 Answers1