I have a table that looks like this in MySQL:
id | date | num | avg
I have found that I can use this for rows to columns and it works so far:
SELECT CONCAT(id, '-', date), MAX(IF(`num` = 0, avg, NULL)) num0 FROM table GROUP BY id, date;
The problem is I have 4000 'num' values (values are integers from 0 to 4000) and I do not want to write 4000 MAX statements manually. Is there anyway I can put a loop in there somehow? Ideally, I would like to have 4030 columns...
I have edited my post to give some more context to the problem I am facing. I am sorry as I should have probably done this earlier.
Context of problem: I have to migrate data from MySQL to HBase. I want to import the values so that each day (which would be represented by a row key along with the id) have many columns (num) which have a value (avg). The column family is avg. I was originally planning on selecting Sqoop's "Select statement" but I understand I may be pushing the boundaries regarding MySQL's columns and I am not sure what the max is. If anyone knows a better way of doing this and can share with me, that would be great.