Does anyone know how I can pass the rows of a MySql query to columns? I have been searching and I find that SUM or GROUP_CONCAT can be used but I do not fully understand the examples, thank you in advance.
I managed to solve that problem with the help of @Voiser, thank you very much! :)
I leave you an example that is what you are looking for, in the example a product table, the scheme is created and a insert is made:
CREATE TABLE products
(
prod_id INT NOT NULL,
prod_name VARCHAR(50) NOT NULL,
PRIMARY KEY (prod_id)
);
INSERT INTO products (prod_id, prod_name)
VALUES (1, 'Shoes'), (2, 'Pants'), (3, 'Shirt');
CREATE TABLE reps
(
rep_id INT NOT NULL,
rep_name VARCHAR(50) NOT NULL,
PRIMARY KEY (rep_id)
);
INSERT INTO reps (rep_id, rep_name)
VALUES (1, 'John'), (2, 'Sally'), (3, 'Joe'), (4, 'Bob');
CREATE TABLE sales
(
prod_id INT NOT NULL,
rep_id INT NOT NULL,
sale_date datetime NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (prod_id, rep_id, sale_date),
FOREIGN KEY (prod_id) REFERENCES products(prod_id),
FOREIGN KEY (rep_id) REFERENCES reps(rep_id)
);
INSERT INTO sales (prod_id, rep_id, sale_date, quantity)
VALUES
(1, 1, '2013-05-16', 20),
(1, 1, '2013-06-19', 2),
(2, 1, '2013-07-03', 5),
(3, 1, '2013-08-22', 27),
(3, 2, '2013-06-27', 500),
(3, 2, '2013-01-07', 150),
(1, 2, '2013-05-01', 89),
(2, 2, '2013-02-14', 23),
(1, 3, '2013-01-29', 19),
(3, 3, '2013-03-06', 13),
(2, 3, '2013-04-18', 1),
(2, 3, '2013-08-03', 78),
(2, 3, '2013-07-22', 69);
A vicious and vulgar SELECT is realized:
SELECT
r.rep_name,
p.prod_name,
s.sale_date,
s.quantity
FROM reps r
INNER JOIN sales s
ON r.rep_id = s.rep_id
INNER JOIN products p
ON s.prod_id = p.prod_id
Now make the row change by columns:
SELECT
r.rep_name,
SUM(CASE WHEN p.prod_name = 'Shoes' THEN s.quantity ELSE 0 END) AS Shoes,
SUM(CASE WHEN p.prod_name = 'Pants' THEN s.quantity ELSE 0 END) AS Pants,
SUM(CASE WHEN p.prod_name = 'Shirt' THEN s.quantity ELSE 0 END) AS Shirt
FROM reps r
INNER JOIN sales s
ON r.rep_id = s.rep_id
INNER JOIN products p
ON s.prod_id = p.prod_id
GROUP BY r.rep_name;
To show the repetitions in each column and the products in the rows:
SELECT
p.prod_name,
SUM(CASE WHEN r.rep_name = 'John' THEN s.quantity ELSE 0 END) AS John,
SUM(CASE WHEN r.rep_name = 'Sally' THEN s.quantity ELSE 0 END) AS Sally,
SUM(CASE WHEN r.rep_name = 'Joe' THEN s.quantity ELSE 0 END) AS Joe,
SUM(CASE WHEN r.rep_name = 'Bob' THEN s.quantity ELSE 0 END) AS Bob
FROM products p
INNER JOIN sales s
ON p.prod_id = s.prod_id
INNER JOIN reps r
ON s.rep_id = r.rep_id
GROUP BY p.prod_name;
If you want to try this example I leave you a demo:
Ideally you would have put a table example, but you can use GROUP BY and MAX to "simulate" a pivot.