Xtykutl

I need to transpose data from rows to columns in MySQL but without any aggregation.

For example, I have the following Table:

 CREATE TABLE `MyTable` ( `id` INT NOT NULL AUTO_INCREMENT ,`city` VARCHAR(10) NOT NULL , 

`category` VARCHAR(10) NOT NULL , `item`VARCHAR(10) NOT NULL , `price` DECIMAL(5,2) NOT NULL , 

`date` DATE NOT NULL ,PRIMARY KEY (`id`)) ENGINE = InnoDB;

with the following data:

INSERT INTO `MyTable` (`id`, `city`, `category`, `item`, `price`, `date`) VALUES (NULL, 'A', 'Cat1','It1', '10.00', '2018-01-01'), 

(NULL, 'A', 'Cat1','It1', '20.00', '2018-01-02'), 

(NULL, 'A', 'Cat1','It2', '30.00', '2018-01-03'),

(NULL, 'A', 'Cat2','It1', '40.00', '2018-01-04'),

(NULL, 'B', 'Cat1','It1', '50.00', '2018-01-05'),

(NULL, 'A', 'Cat1', 'It1', '100.00', '2018-02-11'), 

(NULL, 'A', 'Cat1','It1', '200.00', '2018-02-12'), 

(NULL, 'A', 'Cat1','It2', '300.00', '2018-02-13'),

(NULL, 'A', 'Cat2','It1', '400.00', '2018-02-14'),

(NULL, 'B', 'Cat1','It1', '500.00', '2018-02-15')

When I use this query

 SELECT city, category, item, sum(CASE WHEN EXTRACT(YEAR_MONTH FROM `date`) = "201801" 

THEN price ELSE 0 END) AS "Jan", sum(CASE WHEN EXTRACT(YEAR_MONTH FROM `date`) = "201802" 

THEN price ELSE 0 END) AS "Feb" FROM MyTable WHERE 1 GROUP BY city, category, item

then I get aggregated result for 'Item1':

But I need all transactions without any aggregation:

How should I compose the query in order to get the right result?

The general class of what you are trying to do is called a PIVOT. In a PIVOT, subsets of rows act like tables, in the sense that the data from a single column gets divvied up into several columns. JOIN can help you here:

SELECT

  tblItems.city, tblItems.category, tblItems.item,

  tblJan.price AS Jan,

  tblFeb.price AS Feb

FROM

  (SELECT city, category, item

   FROM MyTable

   GROUP BY city, category, item) AS tblItems

INNER JOIN

  (SELECT city, category, item, price

   FROM MyTable

   WHERE EXTRACT(YEAR_MONTH FROM `date`) = "201801") AS tblJan

ON (tblItems.city = tblJan.city AND tblItems.category = tblJan.category AND tblItems.item = tblJan.item)

INNER JOIN

  (SELECT city, category, item, price

   FROM MyTable

   WHERE EXTRACT(YEAR_MONTH FROM `date`) = "201802") AS tblFeb

ON (tblItems.city = tblFeb.city AND tblItems.category = tblFeb.category AND tblItems.item = tblFeb.item)

ORDER BY tblItems.city, tblItems.category, tblItems.item;

SQL Fiddle: http://sqlfiddle.com/#!9/f77978a/3

However, your very next question is going to be: "How can I do this dynamically, If I don't know ahead of time how many sub-tables I will have?" In the case at hand, it might not matter, because there are a fixed number of months in a year. But sooner or later, you are going to want to perform a PIVOT dynamically.

Some RDBMSes have a built-in PIVOT function, but unfortunately, MySQL isn't one of them. So, for MySQL, you need logic to dynamically build the PIVOT query, and then execute the built query. That logic can be either in a stored procedure within MySQL, or in an external language that has library functions for MySQL (most modern languages do).

EDIT: Or, you can consider following the comment from @Madhur Bhaiya, and delegate the display of the information to the program that fetches the data from your database; SQL is powerful, but -- as with everything else -- it's not the right place to answer all your needs.