0

Good afternoon, i want to ask about MySql query.

I have the initial data from first query as pict below (can dymically) base on where clause using this query SELECT company, discipline_name from tb_data WHERE nfc_id <>"" and valid_start = '2023-07-12' group by category ORDER by category asc.

enter image description here

And from second query i get the data as pict on bellow using this query SELECT company, discipline_name, count(global_id) AS Total from tb_data WHERE nfc_id <>"" and valid_start = '2023-07-12' group by company,category ORDER by category asc;.

enter image description here

Then the result i wanted like pict on below. enter image description here

my question is, how to create dynamicaly column like the result i wanted. because the count of column sometimes is changed base on valid_start where clause. Using stored procedure or how?

Thank You, Regards

Improve this question
7
  • There are a lot more examples of [pivot-table] in stackoverflow.com . However, the cross-tabs is not discussed much.
    – Rick James
    Commented Jul 18, 2023 at 21:09
  • 1
    Consider writing the "pivot" using mysql.rjweb.org/doc.php/pivot . However, embelish it to get the horizontal and vertical totals, too.
    – Rick James
    Commented Jul 18, 2023 at 21:11
  • And please fix the SELECTs to have the same column names as the images.
    – Rick James
    Commented Jul 18, 2023 at 21:12
  • Hai Rick, correct. Thats looks like pivot table, and i can do it with use select (sum if). But my Goal is find the way to automatically create column based on category selected on first query. I will read your sugested link first. Thank you so much.
    – epm.007
    Commented Jul 19, 2023 at 5:39
  • 1
    My link shows how to discover the columns needed from the data.
    – Rick James
    Commented Jul 19, 2023 at 16:32

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.