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

^^ Yes, because `MAX()` will compare them by collation and will exclude the NULLs from the result set to collapse down to a single row. Otherwise, looks good. — Michael Berkowski, Jan 17 '16 at 00:45
@JorgeCampos in my statement `case when itemname = "address" then itemvalue end as address,` do you know how I could assign `n/a` if `address` row is empty? — Abdullah Rasheed, Jan 17 '16 at 02:01
@inspired you can use the [IFNULL Function](http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull) — Jorge Campos, Jan 17 '16 at 03:54

lemon · Answer 1 · 2022-07-31T15:10:11.150

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

Check the demo here.

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?

MySQL - Rows to columns with strings

1 Answers1