combining 1 column of mysql table with every other column (cols a,b,c,d becomes a-b, a-c, a-d)

I have a mysql table:

-----------------------------------------------------------
time     |     host     |      var1     |      var2    ....
20110101 |     hostA    |      7        |      8       ....
20110102 |     hostB    |      2        |      3       ....
20110102 |     hostA    |      4        |      2       ....
20110103 |     hostC    |      9        |      9       ....
20110104 |     hostC    |      1        |      1       ....
20110104 |     hostA    |      10       |      2       ....
.... etc ......
-----------------------------------------------------------

and I want t开发者_JAVA技巧o create new tables for each variable (var1, var2, var3, etc), that look like (for var1):

-----------------------------------------------------------
time     |   hostA-var1 |   hostB-var1  |   hostC-var1 
20110101 |     7        |      null     |      null    ....
20110102 |     4        |      2        |      null    ....
20110103 |     null     |      null     |      9       ....
20110104 |     10       |      null     |      1       ....
.... etc ......
-----------------------------------------------------------

The original table is fairly large so performance is a concern. I'm not very proficient at mysql, the only way I can think to do it is to extract tables that are unique per host and then doing a join on time. Therefore (for var1):

create tempTableA select time, var1 as hostA-var1 from mainTable where host=hostA

create tempTableB select time, var1 as hostB-var1 from mainTable where host=hostB

and then doing a join on all of the tempTables using the time variable.

Search Google for PIVOT examples

select time,
  MAX(case when host='hostA' then var1 end) `hostA-var1`,
  MAX(case when host='hostA' then var2 end) `hostA-var2`,
  MAX(case when host='hostA' then var3 end) `hostA-var3`,
  MAX(case when host='hostB' then var1 end) `hostB-var1`,
  MAX(case when host='hostB' then var2 end) `hostB-var2`,
  MAX(case when host='hostB' then var3 end) `hostB-var3`,
  .....
from tbl
group by time

If you are only looking to merge two fields in a query, I would recommend the CONCAT function.

Say you need to match var1 merged with var2, you would use

CONCAT(`var1`,`var2`)

So you have to find rows that have a var1 and var2 with a period inbetween, you would use:

$sql = "SELECT * FROM `table` WHERE CONCAT(`var1`,'.',`var2`)='$input'";

And if you wanted to output var1 and var2 with a period inbetween:

$sql = "SELECT CONCAT(`var1`,'.',`var2`) AS `var1andvar2` FROM `table`";

Hope that helps some.