Making row value as column MYSQL

Question

I want to group the item and use the distinct value in company_name as my column name

// this is query in MYSQL

select tbl_addcolumn.quantity,tbl_addcolumn.unit,tbl_addcolumn.item,tbl_supplier.company_name,tbl_supplierprice.totalPrice,tbl_addcolumn.totalcost
FROM tbl_addcolumn
inner join tbl_supplierprice 
on tbl_addcolumn.id = tbl_supplierprice.itemName
inner join tbl_supplier
on tbl_supplierprice.fk_supplierId = tbl_supplier.id 
where tbl_supplier.pr_no = '2023-03-0001'  
ORDER BY `tbl_addcolumn`.`item` ASC

quantity	unit	item	company_name	totalPrice	totalcost
5	rim	A4 bond paper	EGB Trading	300	750
5	rim	A4 bond paper	Goodluck Trading	500	750
50	piece	black pen	EGB Trading	300	250
50	piece	black pen	Goodluck Trading	200	250
50	piece	blue pen	EGB Trading	100	250
50	piece	blue pen	Goodluck Trading	200	250
50	piece	red pen	EGB Trading	100	250
50	piece	red pen	Goodluck Trading	200	250

My desire output is something like this:

quantity	unit	item	EGB Trading	Goodluck Trading	totalcost
5	rim	A4 bond paper	300	500	750
50	piece	black pen	300	200	250
50	piece	blue pen	100	200	250
50	piece	red pen	100	200	250

There are lots of solutions posted for [mysql, pivot-table](https://stackoverflow.com/questions/tagged/mysql+pivot-table) already. I recommend you check some of those out to see if you can gain an understanding of the techniques. — Bill Karwin, Apr 05 '23 at 03:19

score 1 · Accepted Answer · answered Apr 05 '23 at 10:05

you can do this using a CASE statement .

sample code

    SELECT 
      date,
      MAX(CASE WHEN item = 'Item 1' THEN value END) AS 'Item 1',
      MAX(CASE WHEN item = 'Item 2' THEN value END) AS 'Item 2',
      MAX(CASE WHEN item = 'Item 3' THEN value END) AS 'Item 3'
    FROM mytable
    GROUP BY date;

your updated query,

SELECT 
  tbl_addcolumn.quantity,  tbl_addcolumn.unit, tbl_addcolumn.item,  tbl_supplier.company_name,
  MAX(CASE WHEN tbl_supplierprice.currency = 'EGB Trading' THEN tbl_supplierprice.totalPrice END) AS 'EGB Trading',
  MAX(CASE WHEN tbl_supplierprice.currency = 'Goodluck Trading' THEN tbl_supplierprice.totalPrice END) AS 'Goodluck Trading',
  tbl_addcolumn.totalcost
FROM tbl_addcolumn
INNER JOIN tbl_supplierprice   ON tbl_addcolumn.id = tbl_supplierprice.itemName
INNER JOIN tbl_supplier  ON tbl_supplierprice.fk_supplierId = tbl_supplier.id 
WHERE tbl_supplier.pr_no = '2023-03-0001'  
GROUP BY 
  tbl_addcolumn.quantity,
  tbl_addcolumn.unit,
  tbl_addcolumn.item,
  tbl_supplier.company_name,
  tbl_addcolumn.totalcost
ORDER BY 
  tbl_addcolumn.item ASC;

let me know the feedback if this works.

Making row value as column MYSQL

1 Answers1