0

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...

Edit:

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.

  • 3
    Returning 4030 columns of data is not something you should be doing in a SQL statement. – Taryn Apr 3 '14 at 15:08
  • I need to do this in order to migrate it to HBase with a proper schema utilizing Sqoop. – dunce1 Apr 3 '14 at 16:20
1

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 !!!

  • Yes, I agree...The amount of columns is definitely not optimal for MySQL. I have tried this path (and it worked) but the problem is that I need to store these values into separate columns in HBase when migrating and it seems like Sqoop only migrates the data as stored in MySQL. If there was a way to somehow map the comma delimited data in each cell when storing into separate columns then that would be a great help although I am not sure if Sqoop can do this. – dunce1 Apr 3 '14 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.