0

There was a solution provided by this thread: MySQL - Rows to Columns. Yet I felt like it's not dynamic. I mean, what if people will add a new field value that hadn't declared? So the developer needs to change the query again in order to make it work? I don't want something like that. I want the query to last even when there are a lot of additional field values. Below is one of the procedures provided as answer.

 create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

Let say, a new value - "D" has been added. The primary solution in order for the D values to be selected in query is to change the query and add a line: case when itemname = "D" then itemvalue end as D. And if another value has been added? It will never end. I need something like this:

SELECT CASE WHEN itemname IS anything THEN itemvalue END AS anything FROM history;

How to make a query that will make the thing possible? Please help. Thanks!

JJ Labajo
  • 296
  • 1
  • 16
  • 1
    You can do this with dynamic SQL. – Gordon Linoff Oct 02 '18 at 11:24
  • how can i do that? – JJ Labajo Oct 02 '18 at 11:26
  • 2
    You could search for mysql dynamic rows to columns - for example https://stackoverflow.com/questions/17964078/mysql-query-to-dynamically-convert-rows-to-columns-on-the-basis-of-two-columns – P.Salmon Oct 02 '18 at 11:28
  • Those aren't dynamic. The query needs to compare the field value first before you make it as a column. What I exactly need is a query that will do the thing regardless of the field value, thus, it should not do the `case when item = 1 then price end` all over again. – JJ Labajo Oct 02 '18 at 11:34
  • 2
    I don't think you read (or understood) the full solution. – P.Salmon Oct 02 '18 at 11:38
  • 1
    "Those aren't dynamic." @P.Salmon and Gordon Linoff are right in this situation.. You need to go dynamic SQL for this with `PREPARE` and `EXECUTE`.. No other way possible in MySQL. – Raymond Nijland Oct 02 '18 at 11:41

0 Answers0