I want to transpose mysql rows to columns using Dynamic query i accomplished the query with expected results. the Query is working in my localhost PhpMyAdmin version 5.2.0 without any error but I’am getting a syntax error in other PhpMyAdmin versions.
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM( CASE WHEN product_code = "', product_code, '" THEN available_quantity ELSE 0 END) AS `', product_code,'`') )
INTO @sql FROM west_stock_details;
SET @sql = CONCAT('SELECT ', @sql, ', SUM(available_quantity) as TOTAL FROM west_stock_details where consignee_name IN ("PARKSON PACKAGING LTD.", "PARKSONS PACKAGING LIMITED", "PARKSONS PACKAGING LIMITED.", "PARKSONS PACKAGING LTD.", "PARKSONS PACKAGING LTD.(PUNE)") and stor_loc_desc NOT IN ("BCM PG6 SL WH", "Quality HOLD Mat", "BCM PG5 MFS WH", "BCM PG4 MFS WH", "BCM PG7 MFS WH", "BCM PG4 SL WH", "BCM PG7 SL WH", "Damaged Stocks", "BCM PM1A MFS WH", "Bad Quality Matl", "MPBC-Bad quality", "BCM PG6 SL WH", "BCM PG7 SL WH") and storage_location NOT IN ("T400","BSL6","BC15","BC14","BC17","BSL4","BSL7","DS01","BC1A","BC04","BE06","BSL7","BSL","BCIC","MPSL","UVSL","T203","BSLA") and storage_location not like "%SL%" GROUP BY destination');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
Error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' SUM(available_quantity) as TOTAL FROM west_stock_details where consignee_name I' at line 1.
Here is the dbfiddle link : https://dbfiddle.uk/LBNyLPfb.
is there any suggestions or help, would be appreciated.
>Solution :
SET SESSION group_concat_max_len = 65535;
This must fix your problem.