Dynamically append rows into columns in mysql

Databases
1

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

1585×649 40.1 KB

2

You wether mentioned a problem, nor what you have already tried.

3 
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

4

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
5

I was wondering whether to move it, 'Tis done now!

2 Likes
6

you’re looking for PIVOT

which version of SQL are you using?

8

OP says:

Mysql version is ‘5.7.19-log’

9

okay, PIVOT is out, then

see https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.