mysql 大量列 动态变量,超过3000列的mySql动态数据透视查询

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

解决方案

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