MySQL - Rows to columns with strings

Question

I've read this answer here, MySQL - Rows to Columns, and I've been able to apply it for my situation up until the aggregate function (Step 3).

I'm trying to convert the rows in itemname to columns for the rows in itemvalue in the following table:

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | address  | 12 street |
|      2 | email    | so@gmail  |
|      3 | name     | legend    |
+--------+----------+-----------+

I applied the following code:

create view table_extended as (
  select
    history.*,
    case when itemname = "address" then itemvalue end as address,
    case when itemname = "email" then itemvalue end as email,
    case when itemname = "name" then itemvalue end as name
  from history
);

And now I have the table_extended:

+--------+----------+-----------+-------------+----------+---------+
| hostid | itemname | itemvalue | address     | email    | name    |
+--------+----------+-----------+-------------+----------+---------+
|      1 | address  | 12 street |   12 street | NULL     | NULL    |
|      1 | email    | so@gmail  |        NULL | so@gmail | NULL    |
|      1 | name     | legend    |        NULL | NULL     | legend  |
+--------+----------+-----------+------+------+----------+---------+

In step 3 he uses sum to aggregate the extended table which all of the values are integers. I tried creating another view, with the following code, but of course these are strings so it turned all of rows except for hostid to 0.

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(address) as address,
    sum(email) as email,
    sum(name) as name
  from history_extended
  group by hostid
);

It looked like this:

+--------+-------------+----------+---------+
| hostid | address     | email    | name    |
+--------+-------------+----------+---------+
|      1 | 0           | 0        | 0       |
+--------+----------+-----------+------+----+

How can I consolidate all of the rows with NULL in order to get the following:

+--------+-------------+----------+---------+
| hostid | address     | email    | name    |
+--------+----------+-----------+-----------+
|      1 | 12 street   | so@gmail | legend  |
+--------+----------+-----------+------+----+

Answer 1

You can use the GROUP_CONCAT string function. This function tries to concatenate the strings matched on the GROUP BY clause and when encounters a NULL value, it just ignores it.

SELECT 
    hostid,
    GROUP_CONCAT(address)   AS address,
    GROUP_CONCAT(email)     AS email,
    GROUP_CONCAT(name)      AS name
FROM ( 
    SELECT 
        history.*,
        CASE WHEN itemname = "address" THEN itemvalue END AS address,
        CASE WHEN itemname = "email" THEN itemvalue END AS email,
        CASE WHEN itemname = "name" THEN itemvalue END AS name
    FROM  
        history
) history_extended
GROUP BY 
    hostid

Here's a fiddle for double checking: https://www.db-fiddle.com/f/36M5CkwjWrzpdAc6qxyVx9/0.

Note: I've noticed that in your first reference of the table you have three different host ids while in the following one you're referencing only one host id. Are there some typos?