Adding column usign extract(year_month) in result query

I have a doubt when usign extract(year_month).

Follow below my query:

SELECT products.sku, SUM(sells_products.quantity) AS quantity, EXTRACT(YEAR_MONTH FROM sells.date) AS period FROM sells, sells_products, products where sells.id = sells_products.id_sell AND (products.sku = '1111' or products.sku = '2222') AND sells_products.id_product = products.id AND sells.date BETWEEN '2023-05-01' AND '2023-07-12' GROUP BY products.sku, EXTRACT(YEAR_MONTH FROM sells.date)

Look below my result:

 SKU | QNT| PERIOD
1111 | 13 | 202305
1111 | 44 | 202306
1111 | 24 | 202307
2222 | 64 | 202305
2222 | 12 | 202306
2222 | 56 | 202307

So, I need make a query that each period (year and month) show a columns in result query, example below:

 SKU | 202305 | 202306 | 202307
1111 |   13   |   44   |   24
2222 |   64   |   12   |   56

However, I not want use if case in query, because I need that query make the columns automatically from my period date in query:

sells.date BETWEEN '2023-05-01' AND '2023-07-12'

Thanks!

  • 1

    Sorry, that’s the way SQL works. All columns in the select-list must be fixed when you prepare the query. The query can’t make more columns automatically during execution.

    – 

  • Is this a PHP problem, or a MySQL problem?

    – 

  • After getting query, using PHP+array is allowed ? With an associative array, you can easily create a table as you need.

    – 

Leave a Comment