mySql Dynamic Pivot Query with over 3000 columns

Question

Good Day, I'm using a dynamic pivot query to generate a cross tab of product sales by month. There are just over 3K products so that means over 3k columns. When I run the query I get an error. If I limit the number of rows in the original table to under 1586 it runs fine with an output of 16 col, including the 'date' field. I can't figure this out and need some help! Please see the code and error below:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when PSHDSTK = ''',
      PSHDSTK,
      ''' then MthSales end) AS `',
      PSHDSTK,
      '`'
    )
  ) INTO @sql
FROM  salesbyrow;

SET @sql = CONCAT('SELECT thedate, ', @sql, ' 
                  FROM salesbyrow
                                    GROUP BY thedate');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

the error I get is

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM salesbyrow
                                    GROUP BY thedate' at line 2

Won't the output be more manageable if you have 3000 _rows_ and as many _columns_ as you have months? — Rick James, Apr 06 '16 at 04:35
It would be but not for my purposes. I need it for a forecasting algorithm and it requires the data in this wide format. — user2588110, Apr 06 '16 at 11:54
OK. You may have to do the pivoting in application code. That is, fetch 3 columns: month+product+amount; then do the pivot/crosstab. — Rick James, Apr 06 '16 at 16:18

Kamil Gosciminski · Answer 1 · 2016-04-08T15:43:09.653

Everything seems to be working just fine for me on a small sample. Are you sure there are no quotes or something that may break your @sql string in the PSHDSTK column?

Add SELECT @sql for debugging purposes before execution of your statement (shown below).

Also, beware of MySQL max size for a string variable and GROUP_CONCAT. But that should become clear when you view your query before executing it.

If GROUP_CONCAT max length is the limit (1024 by default) you should alter the temporary setting (session-scope) for length of it. It's done by:

SET SESSION group_concat_max_len = 10000 -- to set it to 10 000

Sample:

create table salesbyrow(thedate int, PSHDSTK varchar(2), MthSales int);
insert into salesbyrow(thedate,PSHDSTK,MthSales) 
  values (1, 'a1', 6),(1, 'a2', 5), (1, 'a1', 3);

Your code:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when PSHDSTK = ''',
      PSHDSTK,
      ''' then MthSales end) AS `',
      PSHDSTK,
      '`'
    )
  ) INTO @sql
FROM  salesbyrow;

SET @sql = CONCAT('SELECT thedate, ', @sql, ' FROM salesbyrow GROUP BY thedate');

Sanity check the @sql variable:

select @sql;

Statement looks like this (correct):

SELECT thedate, max(case when PSHDSTK = 'a1' then MthSales end) AS `a1`,max(case when PSHDSTK = 'a2' then MthSales end) AS `a2` FROM salesbyrow GROUP BY thedate

Now executing ...

prepare stmt from @sql;
execute stmt;

Result:

thedate     a1  a2
1           6   5

I think that you may be right. When I increase the size of the product names (column names) my 1586 threshold goes down. Running a length@sql) gives me a limit at 1134. What is the max size on a string variable? Any suggestions on getting around this...provided that this is the issue? — user2588110, Apr 05 '16 at 23:35
Review the @sql variable after it's initialization with GROUP_CONCAT. I believe your columns may not fit there. Check [this answer on SO](http://stackoverflow.com/questions/16722623/mysql-how-to-define-or-get-long-string-variables) — Kamil Gosciminski, Apr 05 '16 at 23:40

score 1 · Answer 2 · answered Apr 06 '16 at 21:03

'Consider me' directed me to what was causing the error and another Stackoverflow post directed me to the solution. stackoverflow.com/questions/2567000/…;. So I increased the SET SESSION group_concat_max_len and that works great! No need for additional coding. I just added.

SET SESSION group_concat_max_len = 250000;

To the top of the query. Works great! I'm fairly new here so I couldn't up vote 'consider me' who really answered the question of what was causing the issue.

mySql Dynamic Pivot Query with over 3000 columns

2 Answers2

Linked