Problem
I looked through the posts but only found SQL Server/Access solutions. I’m looking for a MySQL solution (5.X).
I have a history table with three columns: hostid, itemname, and itemvalue. It will return if I conduct a select (select * from history).
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
+--------+----------+-----------+
| 1 | B | 3 |
+--------+----------+-----------+
| 2 | A | 9 |
+--------+----------+-----------+
| 2 | c | 40 |
+--------+----------+-----------+
How can I get a database query to return something like this?
+--------+------+-----+-----+
| hostid | A | B | C |
+--------+------+-----+-----+
| 1 | 10 | 3 | 0 |
+--------+------+-----+-----+
| 2 | 9 | 0 | 40 |
+--------+------+-----+-----+
Asked by Bob Rivers
Solution #1
a slightly longer and more extensive description of the steps to fix this problem If this is too long, please accept my apologies.
I’ll start with the foundation you’ve provided and use it to define a few phrases that I’ll use throughout this essay. This will be the starting point:
select * from history;
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
| 1 | B | 3 |
| 2 | A | 9 |
| 2 | C | 40 |
+--------+----------+-----------+
This will be our goal, the pretty pivot table:
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
Values throughout history. The y-values in the pivot table will be determined by the hostid column. Values throughout history. x-values will replace the itemname column (for obvious reasons)
When I need to create a pivot table, I use a three-step approach (with an optional fourth step):
Let’s see what happens if we apply these steps to your problem:
Step 1: Pick a few columns that you’re interested in. The y-values are provided by hostid, and the x-values are provided by itemname in the desired result.
Step 2: add more columns to the underlying table. For each x-value, we usually need one column. Remember that itemname is our x-value column:
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 |
+--------+----------+-----------+------+------+------+
We didn’t change the amount of rows; instead, we added more columns. Take note of the NULL pattern: a row with itemname = “A” has a non-null value for new column A but null values for the other new columns.
Step 3: assemble the enlarged table into groups and aggregates. Because the y-values are provided by the hostid, we must group by it:
create view history_itemvalue_pivot as (
select
hostid,
sum(A) as A,
sum(B) as B,
sum(C) as C
from history_extended
group by hostid
);
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | NULL |
| 2 | 9 | NULL | 40 |
+--------+------+------+------+
(Note that each y-value now has its own row.) We’re almost there, folks! All we have to do now is get rid of those pesky NULLs.
Step 4: make it pretty. To make the result set more aesthetically pleasing, we’ll just replace any null values with zeroes:
create view history_itemvalue_pivot_pretty as (
select
hostid,
coalesce(A, 0) as A,
coalesce(B, 0) as B,
coalesce(C, 0) as C
from history_itemvalue_pivot
);
select * from history_itemvalue_pivot_pretty;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
And we’re done — we’ve built a nice, pretty pivot table using MySQL.
When using this process, keep the following in mind:
Known limitations:
Answered by Matt Fenwick
Solution #2
SELECT
hostid,
sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,
sum( if( itemname = 'B', itemvalue, 0 ) ) AS B,
sum( if( itemname = 'C', itemvalue, 0 ) ) AS C
FROM
bob
GROUP BY
hostid;
Answered by shantanuo
Solution #3
Another technique, which is particularly beneficial if you have a large number of objects to pivot, is to let mysql construct the query for you:
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'ifnull(SUM(case when itemname = ''',
itemname,
''' then itemvalue end),0) AS `',
itemname, '`'
)
) INTO @sql
FROM
history;
SET @sql = CONCAT('SELECT hostid, ', @sql, '
FROM history
GROUP BY hostid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FIDDLE Added some extra values to see it working
The default value for GROUP CONCAT is 1000, so if you have a really large query, alter this value before running it.
SET SESSION group_concat_max_len = 1000000;
Test:
DROP TABLE IF EXISTS history;
CREATE TABLE 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);
hostid A B C D
1 10 3 0 0
2 9 0 40 5
3 14 67 0 8
Answered by Mihai
Solution #4
Let’s take use of Matt Fenwick’s suggestion, which helped me solve the problem (many thanks), and reduce it to just one query:
select
history.*,
coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid
Answered by jalber
Solution #5
I’m not sure how much of a difference there is between these two methods, but it’s merely a point of comparison.
SELECT hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'
Answered by haudoing
Post is based on https://stackoverflow.com/questions/1241178/mysql-rows-to-columns