limitation in automatic pivot table creation

Question

I have the following query to create a query that creates a pivot table. If I execute SELECT * FROM @sql I get the text stored in @sql. (sorry for the redundancy...)

The problem is that although I have close to 200 entries in solar.sensors, I only get 16 displayed in the @sql.

Is there any limitation in the number of entries that can be concatenated into a variable (in this case @sql) ?

SELECT
  GROUP_CONCAT(
    CONCAT(
      'MAX(CASE WHEN sensor_id = ', sensor_id,
      ' THEN power ELSE 0 END) AS "',sensor_id,
      '"\n'
    )
  ) INTO @sql 
FROM solar.sensors;

SET @sql = CONCAT('SELECT date, ', @sql, ' 
                  FROM solar.agregated 
                   GROUP BY date');

The content of @sql is:

SELECT date, MAX(CASE WHEN sensor_id = 20 THEN power ELSE 0 END) AS "20"
,MAX(CASE WHEN sensor_id = 21 THEN power ELSE 0 END) AS "21"
,MAX(CASE WHEN sensor_id = 81 THEN power ELSE 0 END) AS "81"
,MAX(CASE WHEN sensor_id = 82 THEN power ELSE 0 END) AS "82"
,MAX(CASE WHEN sensor_id = 150 THEN power ELSE 0 END) AS "150"
,MAX(CASE WHEN sensor_id = 151 THEN power ELSE 0 END) AS "151"
,MAX(CASE WHEN sensor_id = 152 THEN power ELSE 0 END) AS "152"
,MAX(CASE WHEN sensor_id = 154 THEN power ELSE 0 END) AS "154"
,MAX(CASE WHEN sensor_id = 159 THEN power ELSE 0 END) AS "159"
,MAX(CASE WHEN sensor_id = 160 THEN power ELSE 0 END) AS "160"
,MAX(CASE WHEN sensor_id = 164 THEN power ELSE 0 END) AS "164"
,MAX(CASE WHEN sensor_id = 165 THEN power ELSE 0 END) AS "165"
,MAX(CASE WHEN sensor_id = 166 THEN power ELSE 0 END) AS "166"
,MAX(CASE WHEN sensor_id = 167 THEN power ELSE 0 END) AS "167"
,MAX(CASE WHEN sensor_id = 168 THEN power ELSE 0 END) AS "168"
,MAX(CASE WHEN sensor_id = 169 THEN power ELSE 0 END) AS "169"
                  FROM solar.agregated /*This is a view*/
                   GROUP BY date

And how many *distinct* `sensor_id` values are present in `solar.sensors` table? — Ravinder Reddy, Feb 16 '15 at 11:27
all the `sensor_id` are different, since it is the primary key — Santi Peñate-Vera, Feb 16 '15 at 11:28

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

The question is answered here: group_concat was cut when running a query on table

basically there is a limit in the number of characters that can be concatenated. One can modify such limit with this command:

SET group_concat_max_len=150000000; /*or a very high number*/

edited May 23 '17 at 12:06

Community

1
1

answered Feb 16 '15 at 11:40

Santi Peñate-Vera

1,053
4
33
68

limitation in automatic pivot table creation

1 Answers1