I have a table called "revisions" in mysql, which has its id field, product_id, date (datetime). This table has only one record per month and I need you to throw the result of the query to the side instead of rows down, that is:
select * from revisiones
=
id, product_id, date (Month1), date (Month2), date (Month3), etc
Is there any way to do this?
This should work for you.
You can see that a column called dates is created using GROUP_CONCAT . Each value has a separator, in this case I used | . If you want to treat each value as an individual column, you can do it with a split. If you only want the month, MySQL has functions to obtain only the month of a given date.
MySQL 5.6 Schema Setup :
CREATE TABLE productos
('producto_id' int, 'producto_nom' varchar(70))
;
INSERT INTO productos
('producto_id', 'producto_nom')
VALUES
(1, 'Pan'),
(2, 'Vino'),
(3, 'Aceite')
;
CREATE TABLE revisiones
('id' int, 'producto_id' int, 'fecha' date)
;
INSERT INTO revisiones
('id', 'producto_id', 'fecha')
VALUES
(1, 1, '2017-01-10'),
(2, 1, '2017-02-22'),
(3, 1, '2017-03-25'),
(4, 2, '2017-01-20'),
(5, 2, '2017-02-28'),
(6, 2, '2017-02-28'),
(7, 3, '2017-01-06'),
(8, 3, '2017-02-07'),
(9, 3, '2017-03-08')
;
Query 1 :
SELECT
p.producto_nom, GROUP_CONCAT(r.fecha SEPARATOR '|') as fechas
FROM
revisiones r
LEFT JOIN
productos p ON r.producto_id=p.producto_id
GROUP BY p.producto_id
Results :
| producto_nom | fechas |
|--------------|----------------------------------|
| Pan | 2017-03-25|2017-02-22|2017-01-10 |
| Vino | 2017-02-28|2017-02-28|2017-01-20 |
| Aceite | 2017-02-07|2017-01-06|2017-03-08 |
Query 2 :
SELECT
GROUP_CONCAT(r.fecha SEPARATOR '|') as fechas
FROM
revisiones r
GROUP BY r.producto_id
Results :
| fechas |
|----------------------------------|
| 2017-01-10|2017-02-22|2017-03-25 |
| 2017-01-20|2017-02-28|2017-02-28 |
| 2017-01-06|2017-02-07|2017-03-08 |