0

I'm looking for some way to select data with an array as the condition, then convert rows to column, the table contain a lot of records (~100 millions).
This is my Example :

date shop item Total
2023-01-01 Shop A book 2
2023-01-01 Shop B book 2
2023-01-02 Shop D book 1
2023-01-02 Shop A toy 1
2023-01-03 Shop A book 3
2023-01-03 Shop C toy 3
2023-01-03 Shop D toy 3

i Want to make a report with dropdown multiselect for shop category.

When user select Shop A, Shop B, and Shop C, i want the result to look like this :

date shop A Shop B Shop C
2023-01-01 2 2 0
2023-01-02 1 0 0
2023-01-03 3 0 3

How to make SQL fast query to produce these result? I'm using XAMPP MySQL and Node.Js.
Shadow
  • 33,525
  • 10
  • 51
  • 64
karv
  • 1
  • 1
  • Use `WHERE shop IN (...)` to select just the requested shops. Then [pivot](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql?noredirect=1&lq=1) to turn them into columns. – Barmar Feb 09 '23 at 18:55

0 Answers0