Select rows with dynamic names from different table as column name in new table matched by articleId

Question

let's imagine I have the following two tables

CREATE TABLE `articles` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `artId` int(11) NOT NULL,
 `price` float NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `artId` (`artId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `priceHistory` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `artId` int(11) NOT NULL,
 `price` float NOT NULL,
 `checkdate` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And the following data

INSERT INTO `test`.`articles` (`id`, `artId`, `price`) VALUES (NULL, '1', '5'), (NULL, '2', '2'), (NULL, '3', '3'), (NULL, '4', '9.50'), (NULL, '5', '1.3');


INSERT INTO `test`.`priceHistory` (`id`, `artId`, `price`, `checkdate`) VALUES (NULL, '1', '5', '2014-11-10 04:19:56'), (NULL, '1', '8.50', '2014-11-09 04:19:56'), (NULL, '1', '2.5', '2014-11-08 04:19:56'), (NULL, '2', '2', '2014-11-10 04:19:56'), (NULL, '2', '2.5', '2014-11-09 04:19:56'), (NULL, '3', '3', '2014-11-10 04:19:56'), (NULL, '4', '9.50', '2014-11-10 04:19:56'), (NULL, '4', '10', '2014-11-09 04:19:56'), (NULL, '4', '8', '2014-11-08 04:19:56'), (NULL, '4', '7', '2014-11-07 04:19:56'), (NULL, '5', '1.3', '2014-11-10 04:19:56'), (NULL, '5', '2', '2014-11-09 04:19:56');

What I now want to do is the following. I want to select all articles from the table articles and also have every price change / price history entry in the same row. As an example, the final result should look like this:

id  articleId   currentPrice    2014-11-07        2014-11-08    2014-11-09      2014-11-10
1   1           5               NULL              2.5               8.5             5
2   2           2               NULL              NULL              2.5             2
3   3           3               NULL              NULL              NULL            3
4   4           9.5             7                 8                 10              9.5
5   5           1.3             NULL              NULL              2               1.3

Basically, I want to select the dates from another table and put them in as column names and fill in the data accordingly. Is this somehow possible with SELECT? I found multiple ressources on this topic, but they were either for a fixed number of rows and I need it to have it dynamic, because the datetime could reach until next year (extreme example) or they were for just one table. These are the ressources I found

1. Problems to select the correct row from a second table
2. Using MySQL GROUP_CONCAT or PIVOT on multiple joined tables
3. MySQL - How to display row value as column name using concat and group_concat
4. Select dynamic Columns in mysql
5. mysql select dynamic row values as column names, another column as value
6. Mysql query to dynamically convert rows to columns
7. MySQL - Rows to Columns
8. http://buysql.com/mysql/12-how-to-pivot-tables.html

Answer 1

Well, you should have put some more effort trying to adapt one of the solutions you've found for your case, or if you do not understand something there - try harder, ask for the specific things that you do not understand - how to join two tables, how to concatenate strings, how to use variables in MySQL, etc., learn how the things work.

That said, let's take solution #6 for instance - Mysql query to dynamically convert rows to columns, and adapt it for your case.

SET @sql = NULL;

SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'MAX(IF(CAST(checkdate AS date) = ''', CAST(checkdate AS date), ''', h.price, NULL)) AS ', '`', CAST(checkdate AS date), '`'
    )
    ORDER BY checkdate
  )
INTO
  @sql
FROM
  priceHistory;

SET @sql = CONCAT('
  SELECT
    a.id AS articleId,
    a.price AS currentPrice,
    ', @sql, '
  FROM
    articles a LEFT JOIN priceHistory h
  ON
    a.id = h.artId
  GROUP
    BY a.id
');

PREPARE stmt FROM @sql;

EXECUTE stmt;

Et voilà!

+-----------+--------------+------------+------------+------------+--------------------+
| articleId | currentPrice | 2014-11-07 | 2014-11-08 | 2014-11-09 | 2014-11-10         |
+-----------+--------------+------------+------------+------------+--------------------+
|         1 |            5 |       NULL |        2.5 |        8.5 |                  5 |
|         2 |            2 |       NULL |       NULL |        2.5 |                  2 |
|         3 |            3 |       NULL |       NULL |       NULL |                  3 |
|         4 |          9.5 |          7 |          8 |         10 |                9.5 |
|         5 |          1.3 |       NULL |       NULL |          2 | 1.2999999523162842 |
+-----------+--------------+------------+------------+------------+--------------------+

There are three things to fix in your data structure.

1. Remove artId from articles - it is completely redundant.
2. Alter the type of checkdate to date.
3. Never use float for storing money (take a look at the value 1.2999999523162842), use NUMERIC instead!