I have 2 tables 1 is master 2nd is sub_table. i need to append product names and its quantity in column wise which is dynamically coming from database. i have attached the screenshot for your reference.
(dynamically change the rows values into columns)
Thank in Advance
You wether mentioned a problem, nor what you have already tried.
SET @statmt=null;
SELECT GROUP_CONCAT(DISTINCT CONCAT('max(CASE WHEN prod_name = "',prod_name,'" THEN ',prod_qty,' end) AS "',prod_name,'"'))
INTO @statmt FROM sup_prod_det ;
SET @statmt = CONCAT('SELECT sales_stk_sup.st_sup_id,',@statmt,'FROM sales_stk_sup join sup_prod_det on
sales_stk_sup.st_sup_id=sup_prod_det.st_sup_id group by st_sup_id');
PREPARE stmt FROM @statmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
i have tried this, but it throws the wrong result
May be better asking in the ‘database’ section, there’s no PHP content as far as I can see. Should there be a space before “FROM” in that query?
1 Like
I was wondering whether to move it, 'Tis done now!
2 Likes
you’re looking for PIVOT
which version of SQL are you using?
OP says:
Mysql version is ‘5.7.19-log’
okay, PIVOT is out, then
see https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.