MySQL Transpose Row Into Column
Image via CrunchBase
This article explain how to transpose a MySQL table from row to column. Suppose that you have a sales table per item per retail outlet per sales date like this:
Then, you need to show the total sales per item per retail outlet like the following:
For that case, you need to transpose the original table into the second table. Unfortunately, MySQL does not support a function for transposing row into column of a table, so we need to create a query to do so.
For the example above, we need a query like the following:
SELECT ITEM_CODE,
SUM(IF(RETAIL_ID=’A',SALES,0)) AS ‘A’,
SUM(IF(RETAIL_ID=’B',SALES,0)) AS ‘B’,
SUM(IF(RETAIL_ID=’C',SALES,0)) AS ‘C’
FROM RETAIL_INFO
GROUP BY ITEM_CODE
The query above will sum the total sales for each retail outlet ID grouped by item code and shows the result on it’s own column. If you have many retail outlets, then you must create the column for each of them.
Akhmad Daniel Sembiring
vITraining.com – Qualified IT Products, Outsourcing, and Services
Ligarwangi.com – Linux, E-book, Coffee, Gift, etc
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=48ab9ab9-29be-40f8-b40c-123a9d04103f)
Nice query pak.
Siiip…
This is a very static query which will only work if you always have a fixed retail_id. Anytime you have a new one you need to change it. Next if retail_id was not a retail but e.g. a date it is even unusable.
had the same issue but with date – transposing date column into rows
we will create dynamic sql request (with lets say last ten days), and then loop through those days adding new column.
DELIMITER $$
DROP PROCEDURE IF EXISTS `a`.`days`$$
CREATE PROCEDURE `a`.`days` ()
BEGIN
DECLARE l_sql VARCHAR(4000);
DECLARE l_date VARCHAR(10);
# select for dates (2009-11-01, 2009-11-02, etc)
DECLARE cur1 CURSOR FOR
SELECT DISTINCT(d.name) as `date`
FROM `date` d
LIMIT 10;
OPEN cur1;
#start your query
SET l_sql=”SELECT kn.name “;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
LOOP
FETCH cur1 INTO l_date;
# dynamic add to l_sql as many times as many dates you have in cur1
SET l_sql=CONCAT(l_sql,”, SUM(IF(d.name=’”,l_date,”‘,value,0)) AS ‘”,l_date,”‘ “);
END LOOP;
END;
CLOSE cur1;
#finish l_sql
SET l_sql=CONCAT(l_sql,” FROM samples GROUP BY something_id”);
#uncomment to see generated query
#SELECT l_sql;
#now prepare query and execute
SET @sql=l_sql;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END;
$$
DELIMITER