sql - MySQL - Rows to Columns -

i tried search posts, found solutions sql server/access. need solution in mysql (5.x).

i have table (called history) 3 columns: hostid, itemname, itemvalue.
if select (select * history), return

   +--------+----------+-----------+    | hostid | itemname | itemvalue |    +--------+----------+-----------+    |   1    |        |    10     |    +--------+----------+-----------+    |   1    |    b     |     3     |    +--------+----------+-----------+    |   2    |        |     9     |    +--------+----------+-----------+    |   2    |    c     |    40     |    +--------+----------+-----------+ 

how query database return like

   +--------+------+-----+-----+    | hostid |    |  b  |  c  |    +--------+------+-----+-----+    |   1    |  10  |  3  |  0  |    +--------+------+-----+-----+    |   2    |   9  |  0  |  40 |    +--------+------+-----+-----+ 

i'm going add longer , more detailed explanation of steps take solve problem. apologize if it's long.

---

i'll start out base you've given , use define couple of terms i'll use rest of post. base table:

select * history;  +--------+----------+-----------+ | hostid | itemname | itemvalue | +--------+----------+-----------+ |      1 |        |        10 | |      1 | b        |         3 | |      2 |        |         9 | |      2 | c        |        40 | +--------+----------+-----------+ 

this our goal, pretty pivot table:

select * history_itemvalue_pivot;  +--------+------+------+------+ | hostid |    | b    | c    | +--------+------+------+------+ |      1 |   10 |    3 |    0 | |      2 |    9 |    0 |   40 | +--------+------+------+------+ 

values in history.hostid column become y-values in pivot table. values in history.itemname column become x-values (for obvious reasons).

---

when have solve problem of creating pivot table, tackle using three-step process (with optional fourth step):

1. select columns of interest, i.e. y-values , x-values
2. extend base table columns -- 1 each x-value
3. group , aggregate extended table -- 1 group each y-value
4. (optional) prettify aggregated table

let's apply these steps problem , see get:

step 1: select columns of interest. in desired result, hostid provides y-values , itemname provides x-values.

step 2: extend base table columns. typically need 1 column per x-value. recall our x-value column itemname:

create view history_extended (   select     history.*,     case when itemname = "a" itemvalue end a,     case when itemname = "b" itemvalue end b,     case when itemname = "c" itemvalue end c   history );  select * history_extended;  +--------+----------+-----------+------+------+------+ | hostid | itemname | itemvalue |    | b    | c    | +--------+----------+-----------+------+------+------+ |      1 |        |        10 |   10 | null | null | |      1 | b        |         3 | null |    3 | null | |      2 |        |         9 |    9 | null | null | |      2 | c        |        40 | null | null |   40 | +--------+----------+-----------+------+------+------+ 

note didn't change number of rows -- added columns. note pattern of nulls -- row itemname = "a" has non-null value new column a, , null values other new columns.

step 3: group , aggregate extended table. need group hostid, since provides y-values:

create view history_itemvalue_pivot (   select     hostid,     sum(a) a,     sum(b) b,     sum(c) c   history_extended   group hostid );  select * history_itemvalue_pivot;  +--------+------+------+------+ | hostid |    | b    | c    | +--------+------+------+------+ |      1 |   10 |    3 | null | |      2 |    9 | null |   40 | +--------+------+------+------+ 

(note have 1 row per y-value.) okay, we're there! need rid of ugly nulls.

step 4: prettify. we're going replace null values zeroes result set nicer at:

create view history_itemvalue_pivot_pretty (   select      hostid,      coalesce(a, 0) a,      coalesce(b, 0) b,      coalesce(c, 0) c    history_itemvalue_pivot  );  select * history_itemvalue_pivot_pretty;  +--------+------+------+------+ | hostid |    | b    | c    | +--------+------+------+------+ |      1 |   10 |    3 |    0 | |      2 |    9 |    0 |   40 | +--------+------+------+------+ 

and we're done -- we've built nice, pretty pivot table using mysql.

---

considerations when applying procedure:

• what value use in columns. used itemvalue in example
• what "neutral" value use in columns. used null, 0 or "", depending on exact situation
• what aggregate function use when grouping. used sum, count , max used (max used when building one-row "objects" had been spread across many rows)
• using multiple columns y-values. solution isn't limited using single column y-values -- plug columns group by clause (and don't forget select them)

known limitations:

• this solution doesn't allow n columns in pivot table -- each pivot column needs manually added when extending base table. 5 or 10 x-values, solution nice. 100, not nice. there solutions stored procedures generating query, they're ugly , difficult right. don't know of way solve problem when pivot table needs have lots of columns.