MySQL - Rows to Columns, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

CREATE TABLE if not exists history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);

INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),(2,'C',40),(2,'D',5),(3,'A',14),(3,'B',67),(3,'D',8);

SELECT distinct concat('<th>',itemname,'</th>') as column_name_table_header FROM history order by 1;

SELECT
     hostid
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else '' end) as col1
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else '' end) as col2
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else '' end) as col3
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else '' end) as col4
FROM history order by 1;

SELECT
     hostid
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else 0 end) as A
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else 0 end) as B
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else 0 end) as C
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else 0 end) as D
FROM history group by hostid order by 1;

SELECT
     hostid
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as a
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as b
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as c
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue end) as d
FROM history group by hostid order by 1;

drop table history;


absolute service time: 0,47 sec