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
– Michael Berkowski, Commented Jan 17, 2016 at 0: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
– Abdullah Rasheed, Commented Jan 17, 2016 at 2:01

lemon · Accepted Answer · 2022-07-31 15:10:11Z

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

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?

edited Jul 31, 2022 at 15:10

answered Apr 12, 2022 at 18:03

lemon

15.7k6 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Rows to columns with strings

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related