SELECT uuid,name,
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f') AS '3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='4641bb5c-e615-11eb-84ff-2cf05dcbfb6f') AS '4641bb5c-e615-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='7be9468e-a0da-11eb-84ff-2cf05dcbfb6f') AS '7be9468e-a0da-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='822e8e40-e617-11eb-84ff-2cf05dcbfb6f') AS '822e8e40-e617-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='90070678-e617-11eb-84ff-2cf05dcbfb6f') AS '90070678-e617-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='997808b0-e617-11eb-84ff-2cf05dcbfb6f') AS '997808b0-e617-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='a45d439d-4e74-11ec-8502-2cf05dcbfb6f') AS 'a45d439d-4e74-11ec-8502-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f') AS 'a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='c678beaf-1c65-11ec-84ff-2cf05dcbfb6f') AS 'c678beaf-1c65-11ec-84ff-2cf05dcbfb6f',
(SELECT sum(quantity) FROM products_stocks WHERE product_uuid = uuid GROUP by product_uuid) AS 'total'
FROM ( SELECT uuid,name FROM products where hidden=0) AS u;
I need to first select all stocks whene hidden=0 and then select quantity for each stock in pivot table, column names must be stocks.uuid
Now I manually defined all needed stocks but it's bad way )
sql fiddle with create and data: http://sqlfiddle.com/#!9/c4a5d03/1
CodePudding user response：
Try a query like this
SELECT DISTINCT
GROUP_CONCAT("SUM(IF(ps.`stock_uuid` = '"
, ps.`stock_uuid`
, "'"
, " ,ps.`quantity`,0 )) AS '",ps.`stock_uuid`,"'") INTO @dyncols
FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS u
INNER JOIN `products_stocks` AS ps ON u.`uuid` = ps.`product_uuid`;
SET @sql = CONCAT("SELECT p.uuid,p.name,",@dyncols,", SUM(ps.`quantity`) AS total FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS p
LEFT JOIN `products_stocks` AS ps ON p.`uuid` = ps.`product_uuid`
GROUP BY p.`uuid`");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sample
MariaDB [test]> SELECT DISTINCT
-> GROUP_CONCAT("SUM(IF(ps.`stock_uuid` = '"
-> , ps.`stock_uuid`
-> , "'"
-> , " ,ps.`quantity`,0 )) AS '",ps.`stock_uuid`,"'") INTO @dyncols
-> FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS u
-> INNER JOIN `products_stocks` AS ps ON u.`uuid` = ps.`product_uuid`;
Query OK, 1 row affected (0.005 sec)
MariaDB [test]>
MariaDB [test]>
MariaDB [test]>
MariaDB [test]> SET @sql = CONCAT("SELECT p.uuid,p.name,",@dyncols,", SUM(ps.`quantity`) AS total FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS p
"> LEFT JOIN `products_stocks` AS ps ON p.`uuid` = ps.`product_uuid`
"> GROUP BY p.`uuid`");
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]>
MariaDB [test]> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.001 sec)
Statement prepared
MariaDB [test]> EXECUTE stmt;
-------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------
| uuid | NAME | 1351467d-be24-11eb-84ff-2cf05dcbfb6f | 3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f | 4641bb5c-e615-11eb-84ff-2cf05dcbfb6f | 7be9468e-a0da-11eb-84ff-2cf05dcbfb6f | 822e8e40-e617-11eb-84ff-2cf05dcbfb6f | 90070678-e617-11eb-84ff-2cf05dcbfb6f | 997808b0-e617-11eb-84ff-2cf05dcbfb6f | a45d439d-4e74-11ec-8502-2cf05dcbfb6f | a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f | c678beaf-1c65-11ec-84ff-2cf05dcbfb6f | 1351467d-be24-11eb-84ff-2cf05dcbfb6f | 3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f | 4641bb5c-e615-11eb-84ff-2cf05dcbfb6f | 7be9468e-a0da-11eb-84ff-2cf05dcbfb6f | 822e8e40-e617-11eb-84ff-2cf05dcbfb6f | 90070678-e617-11eb-84ff-2cf05dcbfb6f | 997808b0-e617-11eb-84ff-2cf05dcbfb6f | a45d439d-4e74-11ec-8502-2cf05dcbfb6f | a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f | c678beaf-1c65-11ec-84ff-2cf05dcbfb6f | total |
-------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------
| 1365caa1-e068-11eb-84ff-2cf05dcbfb6f | Сахар кусковой 1 кг Чайкофский | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 175a2e7a-e084-11eb-84ff-2cf05dcbfb6f | Макароны из твердых сортов пшеницы перья 400 г ALTALIA | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 1f3c3f75-c9b4-11eb-84ff-2cf05dcbfb6f | Бытовая зажигалка пьезо | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 1f94a583-c9b3-11eb-84ff-2cf05dcbfb6f | Батарейки пальчиковые АА 4 шт Luxlite Shock | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 22b9f167-e071-11eb-84ff-2cf05dcbfb6f | Сахар тростниковый демерара светлый 500 г Брауни | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 25321456-ca9d-11eb-84ff-2cf05dcbfb6f | Влажные салфетки антибактериальные 15 шт Belux | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 2cce5997-e070-11eb-84ff-2cf05dcbfb6f | Сахар-песок белый 900 г Чайкофский | 288 | 34 | 38 | 32 | 37 | 36 | 30 | 10 | 60 | 195 | 288 | 34 | 38 | 32 | 37 | 36 | 30 | 10 | 60 | 195 | 760 |
| 42487cfb-c9b2-11eb-84ff-2cf05dcbfb6f | Газ для зажигалки 20 мл | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 56d2d747-ce63-11eb-84ff-2cf05dcbfb6f | Бумажные полотенца 1 рулон 2-слойные белые Belux | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 5c2abd5f-dd55-11eb-84ff-2cf05dcbfb6f | Губка для посуды 5 шт Belux | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 61ae32ba-ca9d-11eb-84ff-2cf05dcbfb6f | Влажные салфетки детские 72 шт Belux | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 628aa141-ca95-11eb-84ff-2cf05dcbfb6f | Чай зеленый 25 пакетиков Мэтр | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| 8adad8b3-e088-11eb-84ff-2cf05dcbfb6f | Макароны из твердых сортов пшеницы рожки 400 г ALTALIA | 288 | 34 | 38 | 32 | 37 | 36 | 30 | 10 | 60 | 195 | 288 | 34 | 38 | 32 | 37 | 36 | 30 | 10 | 60 | 195 | 760 |
| 8b8d08d1-e06b-11eb-84ff-2cf05dcbfb6f | Сахар белый кусковой фигурный Mon Cafe 500 г | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| a3be8062-ce71-11eb-84ff-2cf05dcbfb6f | Туалетная бумага 2-слойная 4 рулона Belux | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| ab88d303-e071-11eb-84ff-2cf05dcbfb6f | Сахар-песок тростниковый демерара светлый 900 г | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| aee12091-c9b1-11eb-84ff-2cf05dcbfb6f | Презервативы classic Domino | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| b14df857-e068-11eb-84ff-2cf05dcbfb6f | Сахар кусковой 500 г Чайкофский | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| b48438bd-ca95-11eb-84ff-2cf05dcbfb6f | Чай черный 25 пакетиков Мэтр | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| b7dba1e7-c9ae-11eb-84ff-2cf05dcbfb6f | Рис Басмати Премиум 1 кг TaMashAe MIADI | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| d2ddad20-c9b2-11eb-84ff-2cf05dcbfb6f | Батарейки мизинчиковые AAA 4 шт Luxlite Shock | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| e584607e-ca9c-11eb-84ff-2cf05dcbfb6f | Носовые платочки белые без аромата Belux | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| f730557a-dd53-11eb-84ff-2cf05dcbfb6f | Туалетная бумага 3-слойная 4 рулона Batist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
| ff3d8825-dd56-11eb-84ff-2cf05dcbfb6f | Салфетки хозяйственные универсальные 110шт Семья и Комфорт | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL |
-------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------
24 rows in set (0.000 sec)
MariaDB [test]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]>
MariaDB [test]>
CodePudding user response：
realized it as advised in comments:
SELECT GROUP_CONCAT(DISTINCT CONCAT('(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid=''',uuid,''') AS ''',uuid,'''')) INTO @sql FROM stocks where hidden=0;
SET @sql = CONCAT('SELECT id as source_id, name,name as _name,slug,thumbnail,price,',@sql,',(SELECT sum(quantity) FROM products_stocks WHERE product_uuid = uuid GROUP by product_uuid) AS total FROM ( SELECT id,uuid,name,slug,thumbnail,price FROM products where hidden=0) AS u having total>0');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;