Depending in your perspective, the preferable approach would be to continue doing it the way you're doing it, but to use SSL to encrypt communications between the application server and the database -- because if you don't, then not only are you sending the encryption key in the clear, you're also sending the decrypted data in the clear.

It seems a little bit pointless to protect the encryption key from packet sniffer snooping, while continuing to transmit the data you're trying to protect, over the same channel, also in the clear.

Granted, the exposure would theoretically be less, since you wouldn't be giving away the key, itself, but you're still exposing the "secure" data.

You could avoid passing the key over the wire by declaring a stored function that returns the key, and using a call to the stored function in place of the constant in the queries, but that creates what is arguably a worse scenario, because a breach of your database's security exposes both the encrypted data and the keys necessary for decryption. But, this would avoid the specific issue that you're intending to avoid.

mysql> CREATE FUNCTION aes_key () RETURNS TINYTEXT DETERMINISTIC NO SQL RETURN 'usa2010';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT aes_key();
+-----------+
| aes_key() |
+-----------+
| usa2010   |
+-----------+

Now, your queries look like AES_ENCRYPT('nebraska', aes_key()). As I say, this does accomplish the direct purpose you intend, and the permissions on the stored function would serve to keep honest people honest, but putting it right out there on the server along with the data seems like a particularly bad idea.

Perhaps slightly more orthodox would be to compile a simple User Defined Function (UDF) in C, which would return a constant string -- the decryption key -- when called, and reference that function in the queries. The only advantage of this approach is that the UDF wouldn't be returned if an attacker did a full database dump to extract the data. One potentially significant disadvantage is that user-defined functions are global and are not controlled by permissions -- they are accessible to any user who can connect to the database server.

And, of course, either of these alternatives, like the mechanism you proposed in the question, involves storing the encryption key on the hard drive of the database server, where it is more vulnerable if the database server is breached. If the key is stored with the application code, then both platforms have to be breached for your sensitive data to be effectively stolen.

So, no... I would suggest that the appropriate place for storing the encryption key would be somewhere other than the database server itself, specifically in the application server, and using SSL to connect from the application to MySQL if data on the wire between the two systems is a security concern.

If you are interested in returning all the values as columns, you need to be try something very adventurous. First look at your query

SELECT 
    CONCAT(id, '-', date),
    MAX(IF(`num` = 0, avg, NULL)) num0 
FROM table 
GROUP BY 
    id, 
    date;

If would be a big mess to get MySQL to execute it as a query by giving MySQL the query with each column formulated.

Perhaps you can get MySQL to concatenate the column values using GROUP_CONCAT. That function was designed for aggregation (or aggravation if you are the actual developer). You can take all the num values can display it as a column-separated listed of numbers like this:

SELECT 
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0))) numlist
FROM table 
GROUP BY 
    id, 
    date;

You can also change the list to be delimited by pipes instead of commas like this:

SELECT 
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0)) SEPARATOR '|') numlist
FROM table 
GROUP BY 
    id, 
    date;

The default maximum length of a GROUP_CONCAT is 1024.

You need to change that max length in the session using this:

SET group_concat_max_len = 10240;

before you issue your query.

Give it a Try !!!